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 ...
Get SQL Pocket Guide, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.