Different Date and Time Formats in MS SQL

Posted: September 27, 2013 in SQL
Tags: , , , , , , ,

Problem
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Different Data Format in MS SQL

Different Data Format in MS SQL

Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:

Format Query Output
1 select convert(varchar, getdate(), 1) 09/27/13
2 select convert(varchar, getdate(), 2) 13.09.27
3 select convert(varchar, getdate(), 3) 27/09/13
4 select convert(varchar, getdate(), 4) 27.09.13
5 select convert(varchar, getdate(), 5) 27-09-13
6 select convert(varchar, getdate(), 6) 27 Sep 13
7 select convert(varchar, getdate(), 7) Sep 27, 13
10 select convert(varchar, getdate(), 10) 09-27-13
11 select convert(varchar, getdate(), 11) 13/09/27
101 select convert(varchar, getdate(), 101) 09/27/2013
102 select convert(varchar, getdate(), 102) 2013.09.27
103 select convert(varchar, getdate(), 103) 27/09/2013
104 select convert(varchar, getdate(), 104) 27.09.2013
105 select convert(varchar, getdate(), 105) 27-09-2013
106 select convert(varchar, getdate(), 106) 27 Sep 2013
107 select convert(varchar, getdate(), 107) Sep 27, 2013
110 select convert(varchar, getdate(), 110) 09-27-2013
111 select convert(varchar, getdate(), 111) 2013/09/27

Different Time Formats

Format Query Output
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Sep 27 2013 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

Get YEAR, MONTH and DAY

SELECT YEAR(GETDATE()) –2013
SELECT MONTH(GETDATE()) –09
SELECT DAY(GETDATE()) –27

Get Year Month Day in MS SQL

Get Year Month Day in MS SQL

You can also format the date or time without dividing characters, as well as concatenate the date and time string:

Sample statement Output
select replace(convert(varchar, getdate(),101),’/’,”) 12302006
select replace(convert(varchar, getdate(),101),’/’,”) + replace(convert(varchar, getdate(),108),’:’,”) 12302006004426
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s