SQL Pocket Guide, 3rd Edition

Datetime Conversions: SQL Server

In SQL Server, you can choose one of four overall approaches to datetime conversion. The CONVERT function is a good general choice, although DATENAME and DATEPART provide a great deal of flexibility when converting to text.

CAST and SET DATEFORMAT

SQL Server supports the standard CAST function and also allows you to specify a datetime format using the SET DATEFORMAT command:

SET DATEFORMAT dmy
SELECT CAST('1/12/2004' AS datetime)

2004-12-01 00:00:00.000

For dates in unambiguous formats, you may not need to worry about the DATEFORMAT setting:

SET DATEFORMAT dmy
SELECT CAST('12-Jan-2004' AS datetime)

2004-01-12 00:00:00.000

When using SET DATEFORMAT, you can specify any of the following arguments: mdy, dmy, ymd, myd, dym.

CONVERT

You can use the CONVERT function for general datetime conversions:

CONVERT(datatype[(length)], expression[, style])

The optional style argument allows you to specify the target and source formats for datetime values, depending on whether you are converting to or from a character string. Table 1-4 lists the supported styles.

For example, you can convert to and from text:

SELECT CONVERT(VARCHAR,
          CONVERT(DATETIME, '15-Nov-1961', 106),
          106)

15 Nov 1961

Use the length argument if you want to specify the length of the resulting character string type. Subtract 100 from most style numbers for two-digit years:

SELECT CONVERT(DATETIME, '1/1/50', 1)

1950-01-01 00:00:00.000

SELECT CONVERT(DATETIME, '49.1.1', 2)

2049-01-01 00:00:00.000

SQL Server uses ...