Date/Time Conversions Using SQL Server
Written By: Edgewood Solutions Engineers -- 1/3/2007
-- read/post comments
-- print --
Rating:
Rate
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
- Find more tips on MSSQLTIPS
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
