Format String to Date

Changing the datatype of a string that represents a date to an actual date datatype is relatively easy using the CAST function. As long as the date string is in a format recognizable to the regional settings defined on your SQL Server, the conversion can take place with a one line command.

There are a few different date datatypes you can choose from. The safest best would be to use the datetime datatype. This is one of the larger date datatypes (8 bytes) that can accommodate dates between the range of ‘1753-01-01 00:00:00.000’ AND ‘9999-12-31 23:59:59.997’

The following string representations of all ‘2012-01-01’ all convert the same with my English (US) settings:

[cc lang=”sql”]
SELECT CAST(‘2012-01-01′ AS DATETIME)

SELECT CAST(’01-01-2012’ AS DATETIME)

SELECT CAST(‘2012/01/01′ AS DATETIME)

SELECT CAST(’01/01/2012’ AS DATETIME)

SELECT CAST(‘010112’ AS DATETIME)

SELECT CAST(‘010112’ AS DATETIME)
[/cc]

SQL Server 2008+ introduced additional date datatypes including DATE & TIME. These data types function as you would expect, only keeping the date without the time, or the time without the date.

[cc lang=”sql”]
SELECT CAST(‘2012-01-01’ AS DATE)
[/cc]

The most compatible string format you can use to represent both date & time together is the following. This allows for easy conversion back and forth:

[cc lang=”sql”]
SELECT CAST(‘2012-01-01T23:05:00.000’ as datetime)
SELECT CAST(‘2012-01-01T23:05:00.000’ as time)
[/cc]

That is the letter T separating the date from the time.