How to convert strings to dates and times to strings with MS SQL Server

SQL Server is very smart when doing conversions, but you can help by guiding it.

  • I am using the example of datetime, but the same applies to smalldatetime, except that smalldatetime resolution is to the nearest minute.

Date and times strings to datetime

  1. SELECT   '2007-07-02T13:14:15.997' AS 'Date Time as char string',
  2.    CAST('2007-07-02 13:14:15.997' AS DATETIME) AS 'Date Time with cast',
  3.    CONVERT(DATETIME, '2007/07/02 13:14:15.997') AS 'Date Time from /',
  4.    CONVERT(DATETIME, 'Jul 02 2007 1:14:15:997PM',109) AS 'Date Time style 109'
  5. GO
Date Time as char string Date Time with cast     Date Time from /        Date Time style 109
------------------------ ----------------------- ----------------------- -----------------------
2007-07-02T13:14:15.997  2007-07-02 13:14:15.997 2007-07-02 13:14:15.997 2007-07-02 13:14:15.997

(1 row(s) affected)
  1. 2007-07-02T13:14:15.997: is the ISO8601 format, with the T to separate the date from the time.
  2. Cast lets SQL Server use its smarts to convert to a datetime.
  3. Convert lets you specify the style the date and time format is in with the style. It's like giving a hint.

Date and times to strings

  1. SELECT CONVERT(VARCHAR(10),GETDATE(),101) AS '10 chars of today converted',
  2.        CONVERT(VARCHAR(5),GETDATE(),101) AS '5 chars of today converted',
  3.        CAST(GETDATE() AS VARCHAR(5)) AS '5 chars of today with cast',
  4.        CAST(GETDATE() AS VARCHAR(25)) AS '25 chars of today with cast'
  5. go
10 chars of today converted 5 chars of today converted 5 chars of today with cast 25 chars of today with cast
--------------------------- -------------------------- -------------------------- ---------------------------
07/02/2007                  07/02                      Jul                        Jul  2 2007  2:52PM

(1 row(s) affected)
  1. convert(varchar(10): Get the first 10 characters of the date that was in style 101, ie: today's date in the format mm/dd/yyyy
  2. convert(varchar(5): Get the first 5 characters of the date that was in style 101, ie: today's date in the format mm/dd/yyyy
  3. cast does not let you control the style, but does the same truncation.