SQL Convert String to DATETIME

Converting a string to a datetime value is a common requirement in database solutions.

Use CONVERT or TRY_CONVERT to perform this operation.

Example

#

This example converts a string to a datetime value.

SELECT CONVERT(DATETIME, '2022-04-28') AS Datetime
Result:  1 record
Datetime
2022-04-28 00:00:00.000

If no time is specified, it will be set to 00:00:00.000.


Converting String to DATETIME

CONVERT and TRY_CONVERT can convert string values to datetime.

CONVERT returns an error when a conversion error occurs.

TRY_CONVERT returns NULL when a conversion error occurs.


Syntax

Syntax of the CONVERT function.

CONVERT(datetime, string-value, [, style])

datetime -- indicates that the specified string-value will be converted to datetime.

string-value -- the string value to be converted.

style -- optional, the format of the input string to be converted.

These are valid style values with associated datetime string formats.

Style Description Format
0 or 100 Default mon dd yyyy hh:miAM/PM
1 or 101 U.S. 1 = mm/dd/yy
101 = mm/dd/yyyy
2 or 102 ANSI 2 = yy.mm.dd
102 = yyyy.mm.dd
3 or 103 British/French 3 = dd/mm/yy
103 = dd/mm/yyyy
4 or 104 German 4 = dd.mm.yy
104 = dd.mm.yy
5 or 105 Italian 5 = dd-mm-yy
105 = dd-mm-yyyy
6 or 106 - 6 = dd mon yy
106 = dd mon yy
7 or 107 - 7 = Mon dd, yy
107 = Mon dd, yyyy
8 or 108 - hh:mi:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM/PM
10 or 110 USA 10 = mm-dd-yy
110 = mm-dd-yyyy
11 or 111 JAPAN 11 = yy/mm/dd
111 = yyyy/mm/dd
12 or 112 ISO 12 = yymmdd
112 = yyyymmdd
13 or 113 Europe default + milliseconds dd mon yyyy hh:mi:ss:mmm(24h)
14 or 114 - hh:mi:ss:mmm(24h)
20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127 ISO8601 with time zone Z. yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130 Hijri dd mon yyyy hh:mi:ss:mmmAM
131 Hijri dd/mm/yyyy hh:mi:ss:mmmAM

More Examples

Convert String to DATETIME with Different Formats

Convert different string formats to datetime values.
SELECT CONVERT(DATETIME, 'Mar 28 2022 05:29 PM', 0) AS 'mon dd yyyy hh:mi(AM/PM)',
       CONVERT(DATETIME, '03/28/2022', 101) AS 'mm/dd/yyyy',
       CONVERT(DATETIME, '28/03/22', 3) AS 'dd/mm/yy',
       CONVERT(DATETIME, '28.02.2022', 104) AS 'dd.mm.yyyy',
       CONVERT(DATETIME, '05:29:01', 8) AS 'hh:mi:ss',
       CONVERT(DATETIME, '2021-11-01 17:29:11', 20) AS 'yyyy-mm-dd hh:mi:ss(24h)'
Result:  1 record
mon dd yyyy hh:mi(AM/PM) mm/dd/yyyy dd/mm/yy dd.mm.yyyy hh:mi:ss yyyy-mm-dd hh:mi:ss(24h)
2022-03-28 17:29:00.000 2022-03-28 00:00:00.000 2022-03-28 00:00:00.000 2022-02-28 00:00:00.000 1900-01-01 05:29:01.000 2021-11-01 17:29:11.000

Values without a datepart are set to 1900-01-01.

Convert String to DATETIME with TRY_CONVERT

Convert different string formats to datetime values.
If a conversion fails, return NULL.
SELECT TRY_CONVERT(datetime, '2022-11-01 05:29 PM', 0) AS 'mon dd yyyy hh:miAM/PM)',
       TRY_CONVERT(datetime, '2022-11-01 05:29 PM', 101) AS 'mm/dd/yyyy',
       TRY_CONVERT(datetime, '2022-11-01 05:29 PM', 3) AS 'dd/mm/yy',
       TRY_CONVERT(datetime, '2022-11-01 05:29 PM', 104) AS 'dd.mm.yyyy',
       TRY_CONVERT(datetime, '2022-11-01 05:29:01 PM', 8) AS 'hh:mi:ss',
       TRY_CONVERT(datetime, '2022-11-01 05:29 PM', 20) AS 'yyyy-mm-dd hh:mi:ss(24h)'
Result:  1 record
mon dd yyyy hh:miAM/PM mm/dd/yyyy dd/mm/yy dd.mm.yyyy hh:mi:ss yyyy-mm-dd hh:mi:ss(24h)
2022-11-01 17:29:00.000 2022-11-01 17:29:00.000 NULL 2022-11-01 17:29:00.000 NULL 2022-11-01 17:29:00.000

CONVERT and TRY_CONVERT accept a variety of date formats. However, sometimes the disconnect between the input string and the specified style is too far apart. In that case TRY_CONVERT returns a NULL.


You may also like



Guides


vsn 3.1