 |
|
Date and Time Conversions Using SQL Server
|
By: Edgewood Solutions | Read Comments (6) | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More |
|
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
- Check out all of the SQL Server tip categories on the site
Last Update: 1/3/2007
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Wednesday, January 07, 2009 - 12:50:38 PM - tosscrosby | Read The Tip |
|
I just had a request from our Oracle team to see if I could supply them with dates in a MMDDYYYYHHMMSS format. Came here and found what I need in all of about 30 seconds. Thanks. |
|
| Thursday, January 08, 2009 - 6:27:17 AM - Senthilkumar.S | Read The Tip |
|
Select the particular year,month, day,pls do following query --------------------------------------------------------------------------------------- select * from barrowBooks where year(barrow_date)='2008' and month(barrow_date)='11' and day(barrow_date)='12' order by barrow_date desc
convert the date and time using this query ---------------------------------------------------------------- select convert(char(11),getdate(),108) -->Result of time 11:18:18 select convert(char(10),getdate(),101) -->Result of Date 11/06/2008 |
|
| Thursday, January 08, 2009 - 6:46:58 AM - tosscrosby | Read The Tip |
|
Actually, I was simply complimenting the "tip" as it provided exactly what I needed for ANY date: select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','') This converts any MSSQL datetime to MMDDYYYYHHMMSS - exactly what the Oracle folks wanted!
Thanks anyway :-) |
|
| Thursday, May 12, 2011 - 11:15:21 AM - Vijay Prakash Vyas | Read The Tip |
|
It's very helpful info thanks for help!!!!! |
|
| Wednesday, May 16, 2012 - 7:42:46 AM - Tessa | Read The Tip |
|
I would like to see the complete date and time a DD-MM-YYYY HH:MM:SS I use: convert(varchar,getdate(),105) + convert(varchar,getdate(),108) I get: 16-05-201213:42:18
How would I get the extra space between the Date and Time to get 16-05-2012 13:42:18 |
|
| Wednesday, May 16, 2012 - 9:23:58 AM - Jeremy Kadlec | Read The Tip | |||
|
Tessa, Would this work for you? convert(varchar,getdate(),105) + ' ' + convert(varchar,getdate(),108) Is there any reason you would not use this format:
Have you also seen these tips: http://www.mssqltips.com/sqlservertip/2588/new-date-and-time-functions-in-sql-server-2012/ http://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/ HTH. Thank you, |
||||
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |