Converting a string to a datetime value is a common requirement.
Use CONVERT or TRY_CONVERT to perform this operation.
This example converts a string to a datetime value.
SELECT CONVERT(DATETIME, '2022-04-28') AS Datetime
| Datetime |
|---|
| 2022-04-28 00:00:00.000 |
If no time is specified, it will be set to 00:00:00.000.
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 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 |
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)'
| 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.
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)'
| 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.
