Thursday, December 2, 2010

Date/Time Conversions Using SQL Server


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.
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:

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30



TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

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

Next Steps
  • The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available
  • These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently 

Let's take Sql Server DateTtime styles example:
Format Query
USA mm/dd/yy select convert(varchar, getdate(), 1)
ANSI yy.mm.dd select convert(varchar, getdate(), 2)
British/French dd/mm/yy select convert(varchar, getdate(), 3)
German dd.mm.yy select convert(varchar, getdate(), 4)
Italian dd-mm-yy select convert(varchar, getdate(), 5)
dd mon yy select convert(varchar, getdate(), 6)
Mon dd, yy select convert(varchar, getdate(), 7)
USA mm-dd-yy select convert(varchar, getdate(), 10)
JAPAN yy/mm/dd select convert(varchar, getdate(), 11)
ISO yymmdd select convert(varchar, getdate(), 12)
mon dd yyyy hh:miAM (or PM) select convert(varchar, getdate(), 100)
mm/dd/yyyy select convert(varchar, getdate(), 101)
yyyy.mm.dd select convert(varchar, getdate(), 102)
dd/mm/yyyy select convert(varchar, getdate(), 103)
dd.mm.yyyy select convert(varchar, getdate(), 104)
dd-mm-yyyy select convert(varchar, getdate(), 105)
dd mon yyyy select convert(varchar, getdate(), 106)
Mon dd, yyyy select convert(varchar, getdate(), 107)
hh:mm:ss select convert(varchar, getdate(), 108)
Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) select convert(varchar, getdate(), 109)
mm-dd-yyyy select convert(varchar, getdate(), 110)
yyyy/mm/dd select convert(varchar, getdate(), 111)
yyyymmdd select convert(varchar, getdate(), 112)
Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13)
hh:mi:ss:mmm(24h) select convert(varchar, getdate(), 114)




    No comments:

    Post a Comment