If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: data quality starts with type conversion. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT handles the documented formats, anything else needs a function of your own.
What you’ll take away:
- when
TRY_CONVERTwith astylecode is enough — and when you need a custom function; - the complete table of supported
stylecodes fordate/datetime/datetime2/time; - four production-ready functions
fnConvertDate,fnConvertTime,fnConvertDateTime,fnConvertDateTime2with a format-string interface instead of magic style numbers; - the Postgres bridge (
TO_DATE,TO_TIMESTAMP) as a 1:1 counterpart for multi-engine ETL; - a FAQ block covering the most common CSV/JSON import pitfalls.
Prerequisites: SQL Server 2017+ (for TRY_CONVERT styles 23/126), PostgreSQL 12+ for the Postgres bridge.
Content
- Overview
- Style Codes
- Format Identifiers
- User-defined Functions
- Postgres Bridge
- Summary
- FAQ
- Related Posts
Overview
The hardest conversion is probably the one that takes a date and/or time supplied as text and turns it into a value of type date, time, datetime or datetime2.
SQL Server offers, among others, the following data types for storing a date and/or time:
- date
- time
- datetime
- datetime2
For converting a date supplied as text, the standard functions CONVERT and TRY_CONVERT identify the date format via the style parameter. For example, style 104 tells SQL Server that the value in the expression parameter is a German date with a four-digit year matching the format string dd.mm.yyyy.
1: TRY_CONVERT(date, ’24.04.2023’, 104) -- 24.04.2023
The supported date formats are listed in the Microsoft documentation under CAST and CONVERT (Transact-SQL). Each format has a style code that is passed as the third parameter to TRY_CONVERT — or to CONVERT — and controls how the date is parsed. The codes are listed in the Style Codes section.
SQL Server also recognises additional format strings — the format identifiers — that the FORMAT function uses to render a date (or a number) as text according to a format string. For example, the date 24.04.2023 12:34:15.123 is rendered as 20230418123415123 by the format string yyyyMMddhhmmssfff.
1: FORMAT(TRY_CONVERT(datetime, N'24.04.2023 12:34:15.123', 104), 'yyyyMMddhhmmssfff')
2: -- 20230418123415123
See the Format Identifiers section for the full list.
Conversely, SQL Server offers no built-in function for the reverse direction — parsing a date written as text according to a format-identifier string. To convert such input, you have to write your own functions. This article presents four user-defined functions that target date, time, datetime and datetime2:
The full code lives in the User-defined Functions section.
The result of each conversion depends on the target data type, which defines the supported value range and — when a time component is present — the precision:
date: 01.01.0001 to 31.12.9999, no time component.time: up to 7 decimal places for fractional seconds (100-nanosecond resolution).datetime: 01.01.1753 to 31.12.9999, granularity ~3.33 ms — fractional seconds are rounded to.000,.003or.007, not freely settable to three decimal places.datetime2: 01.01.0001 to 31.12.9999, up to 7 decimal places, ISO-8601-compliant.
For new development, Microsoft recommends datetime2 over datetime: wider value range, more precise fractional seconds, and ISO-8601-compliant parsing. A detailed comparison with Oracle (DATE, TIMESTAMP) and MySQL (DATETIME, TIMESTAMP) is deliberately out of scope here — value ranges and precisions differ substantially across vendors, and the respective vendor manual is the only reliable source.
Style Codes
| Style | Country / description | Format (yy) | Format (yyyy) |
|---|---|---|---|
| -/0/100 | Default for datetime & smalldatetime | – | mon dd yyyy hh:miAM |
| 1/101 | United States | mm/dd/yy | mm/dd/yyyy |
| 2/102 | ANSI | yy.mm.dd | yyyy.mm.dd |
| 3/103 | British / French | dd/mm/yy | dd/mm/yyyy |
| 4/104 | German | dd.mm.yy | dd.mm.yyyy |
| 10/110 | United States | mm-dd-yy | mm-dd-yyyy |
| 12/112 | ISO | yymmdd | yyyymmdd |
| 13/113 | European default (with ms, 24h) | dd mon yyyy hh:mi:ss:mmm | dd mon yyyy hh:mi:ss:mmm |
Format Identifiers
For the textual representation of a date, Microsoft provides a set of format identifiers. The most relevant ones are summarised below:
| Identifier | Meaning |
| d | Day of the month, from 1 to 31. |
| dd | Day of the month, from 01 to 31. |
| ddd | Abbreviated weekday name. |
| dddd | Full weekday name. |
| f | Tenths of a second in a date/time value. |
| ff | Hundredths of a second in a date/time value. |
| fff | Milliseconds in a date/time value. |
| h | Hour, 1 to 12 (12-hour clock). |
| hh | Hour, 01 to 12 (12-hour clock). |
| H | Hour, 0 to 23 (24-hour clock). |
| HH | Hour, 00 to 23 (24-hour clock). |
| m | Minute, 0 to 59. |
| mm | Minute, 00 to 59. |
| M | Month, 1 to 12. |
| MM | Month, 01 to 12. |
| s | Second, 0 to 59. |
| ss | Second, 00 to 59. |
| yy | Year, 00 to 99. |
| yyyy | Year as a four-digit number. |
User-defined Functions
The four conversion functions take a date as text in the p_date parameter (or p_time for the time variant). The p_date_style parameter accepts either a user-friendly format string (dd.mm.yyyy, yyyy-mm-dd, …) or one of the numeric style codes mentioned above. The format-string vocabulary is similar to the format identifiers from the previous section, but not identical — Microsoft itself is inconsistent in distinguishing format strings from format identifiers, so the functions take a pragmatic approach: they don’t interpret format strings, they translate them into the corresponding style code. The format string is matched case-insensitively.
Declaration
The user-defined functions share the same general shape:
Description
Converts an input value to the target data type date, time, datetime or datetime2. If the input cannot be converted, NULL is returned. The format-string parameter is treated case-insensitively.
Syntax
1: [dbo].[fnConvertDate] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
Arguments
- p_dateThe input value to convert.
- p_date_styleA format string describing how the date in p_date is laid out, or a style code. Both control the parsing. See the function body for the supported format strings and codes.
Return value
Returns the converted value (date, time, datetime or datetime2) on success. If the input cannot be converted, returns NULL. If NULL or an empty string is passed, the function returns NULL.
[dbo].[fnConvertDate]
1: CREATE FUNCTION [dbo].[fnConvertDate] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS date
3: AS
4: BEGIN
5: DECLARE @return_value AS date;
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: ELSE
16: BEGIN
17: SET @style = CASE
18: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
19: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
20: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
21: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
22: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
23: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
24: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
25: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
26: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
27: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
28: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
29: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
30: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
31: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
32: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
33: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
34: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
35: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
36: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
37: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
38: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
39: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
40: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
41: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
42: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
43: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
44: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
45: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
46: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
47: END;
48:
49: IF @style IS NOT NULL
50: SET @return_value = TRY_CONVERT(date, @p_date, @style);
51: ELSE
52: SET @return_value = NULL;
53: END;
54:
55: RETURN @return_value;
56: END;[dbo].[fnConvertTime]
1: CREATE FUNCTION [dbo].[fnConvertTime] (@p_time AS nvarchar(50), @p_time_style nvarchar(50))
2: RETURNS time(7)
3: AS
4: BEGIN
5: DECLARE @return_value AS time(7);
6: DECLARE @style AS int;
7:
8: SET @p_time = LTRIM(RTRIM(@p_time));
9: SET @p_time_style = LOWER(LTRIM(RTRIM(@p_time_style)));
10:
11: IF @p_time IS NULL OR @p_time = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[f...]
16: ELSE IF @p_time_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff',
20: N'yyyymmddhhmmssffff', N'yyyymmddhhmmssfffff',
21: N'yyyymmddhhmmssffffff', N'yyyymmddhhmmssfffffff'
22: )
23: BEGIN
24: SET @return_value = TRY_CONVERT(time(7)
25: ,SUBSTRING(@p_time, 1, 4) + N'-' + -- yyyy
26: SUBSTRING(@p_time, 5, 2) + N'-' + -- mm
27: SUBSTRING(@p_time, 7, 2) + N' ' + -- dd
28: SUBSTRING(@p_time, 9, 2) + N':' + -- hh
29: SUBSTRING(@p_time, 11, 2) + N':' + -- mi
30: SUBSTRING(@p_time, 13, 2) + -- ss
31: CASE WHEN LEN(@p_time) > 14
32: THEN N'.' + SUBSTRING(@p_time, 15, LEN(@p_time) - 14)
33: ELSE N''
34: END
35: );
36: END
37: -- Kompakt-Zeit ohne Trenner: hhmmss[f...]
38: ELSE IF @p_time_style IN
39: (
40: N'hhmmss', N'hhmmssf',
41: N'hhmmssff', N'hhmmssfff',
42: N'hhmmssffff', N'hhmmssfffff',
43: N'hhmmssffffff', N'hhmmssfffffff'
44: )
45: BEGIN
46: SET @return_value = TRY_CONVERT(time(7)
47: ,SUBSTRING(@p_time, 1, 2) + N':' + -- hh
48: SUBSTRING(@p_time, 3, 2) + N':' + -- mi
49: SUBSTRING(@p_time, 5, 2) + -- ss
50: CASE WHEN LEN(@p_time) > 6
51: THEN N'.' + SUBSTRING(@p_time, 7, LEN(@p_time) - 6)
52: ELSE N''
53: END
54: );
55: END
56: -- Klassische MS-style-Codes mit Zeit-Anteil
57: ELSE
58: BEGIN
59: SET @style = CASE
60: WHEN @p_time_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
61: WHEN @p_time_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
62: WHEN @p_time_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
63: WHEN @p_time_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
64: WHEN @p_time_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
65: WHEN @p_time_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
66: WHEN @p_time_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
67: WHEN @p_time_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
68: WHEN @p_time_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
69: WHEN @p_time_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
70: END;
71:
72: IF @style IS NOT NULL
73: SET @return_value = TRY_CONVERT(time(7), @p_time, @style);
74: ELSE
75: SET @return_value = NULL;
76: END;
77:
78: RETURN @return_value;
79: END;
[dbo].[fnConvertDateTime]
1: CREATE FUNCTION [dbo].[fnConvertDateTime] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS datetime
3: AS
4: BEGIN
5: DECLARE @return_value AS datetime;
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[fff]
16: ELSE IF @p_date_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff'
20: )
21: BEGIN
22: SET @return_value = TRY_CONVERT(datetime
23: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
24: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
25: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
26: SUBSTRING(@p_date, 9, 2) + N':' + -- hh
27: SUBSTRING(@p_date, 11, 2) + N':' + -- mi
28: SUBSTRING(@p_date, 13, 2) + -- ss
29: CASE WHEN LEN(@p_date) > 14
30: THEN N'.' + SUBSTRING(@p_date, 15, LEN(@p_date) - 14)
31: ELSE N''
32: END
33: );
34: END
35: -- Sonderform mit Unterstrich-Separator: yyyymmdd_hhmissmmm
36: ELSE IF @p_date_style = N'yyyymmdd_hhmissmmm'
37: BEGIN
38: SET @return_value = TRY_CONVERT(datetime
39: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
40: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
41: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
42: SUBSTRING(@p_date, 10, 2) + N':' + -- hh (nach Unterstrich)
43: SUBSTRING(@p_date, 12, 2) + N':' + -- mi
44: SUBSTRING(@p_date, 14, 2) + N'.' + -- ss
45: SUBSTRING(@p_date, 16, 3) -- mmm
46: );
47: END
48: -- Klassische MS-style-Codes
49: ELSE
50: BEGIN
51: SET @style = CASE
52: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
53: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
54: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
55: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
56: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
57: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
58: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
59: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
60: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
61: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
62: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
63: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
64: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
65: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
66: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
67: WHEN @p_date_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
68: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
69: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
70: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
71: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
72: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
73: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
74: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
75: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
76: WHEN @p_date_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
77: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
78: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
79: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
80: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
81: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
82: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
83: END;
84:
85: IF @style IS NOT NULL
86: SET @return_value = TRY_CONVERT(datetime, @p_date, @style);
87: ELSE
88: SET @return_value = NULL;
89: END;
90:
91: RETURN @return_value;
92: END;
[dbo].[fnConvertDateTime2]
1: CREATE FUNCTION [dbo].[fnConvertDateTime2] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
2: RETURNS datetime2(7)
3: AS
4: BEGIN
5: DECLARE @return_value AS datetime2(7);
6: DECLARE @style AS int;
7:
8: SET @p_date = LTRIM(RTRIM(@p_date));
9: SET @p_date_style = LOWER(LTRIM(RTRIM(@p_date_style)));
10:
11: IF @p_date IS NULL OR @p_date = N''
12: BEGIN
13: SET @return_value = NULL;
14: END
15: -- Kompakt-Datum+Zeit ohne Trenner: yyyymmddhhmmss[f...] (bis 7 Stellen)
16: ELSE IF @p_date_style IN
17: (
18: N'yyyymmddhhmmss', N'yyyymmddhhmmssf',
19: N'yyyymmddhhmmssff', N'yyyymmddhhmmssfff',
20: N'yyyymmddhhmmssffff', N'yyyymmddhhmmssfffff',
21: N'yyyymmddhhmmssffffff', N'yyyymmddhhmmssfffffff'
22: )
23: BEGIN
24: SET @return_value = TRY_CONVERT(datetime2(7)
25: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
26: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
27: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
28: SUBSTRING(@p_date, 9, 2) + N':' + -- hh
29: SUBSTRING(@p_date, 11, 2) + N':' + -- mi
30: SUBSTRING(@p_date, 13, 2) + -- ss
31: CASE WHEN LEN(@p_date) > 14
32: THEN N'.' + SUBSTRING(@p_date, 15, LEN(@p_date) - 14)
33: ELSE N''
34: END
35: );
36: END
37: -- Sonderform mit Unterstrich-Separator: yyyymmdd_hhmissmmm
38: ELSE IF @p_date_style = N'yyyymmdd_hhmissmmm'
39: BEGIN
40: SET @return_value = TRY_CONVERT(datetime2(7)
41: ,SUBSTRING(@p_date, 1, 4) + N'-' + -- yyyy
42: SUBSTRING(@p_date, 5, 2) + N'-' + -- mm
43: SUBSTRING(@p_date, 7, 2) + N' ' + -- dd
44: SUBSTRING(@p_date, 10, 2) + N':' + -- hh
45: SUBSTRING(@p_date, 12, 2) + N':' + -- mi
46: SUBSTRING(@p_date, 14, 2) + N'.' + -- ss
47: SUBSTRING(@p_date, 16, 3) -- mmm
48: );
49: END
50: -- Klassische MS-style-Codes
51: ELSE
52: BEGIN
53: SET @style = CASE
54: WHEN @p_date_style IN (N'0', N'100', N'mon dd yyyy hh:miam') THEN 100
55: WHEN @p_date_style IN (N'1', N'mm/dd/yy') THEN 1
56: WHEN @p_date_style IN (N'101', N'mm/dd/yyyy') THEN 101
57: WHEN @p_date_style IN (N'2', N'yy.mm.dd') THEN 2
58: WHEN @p_date_style IN (N'102', N'yyyy.mm.dd') THEN 102
59: WHEN @p_date_style IN (N'3', N'dd/mm/yy') THEN 3
60: WHEN @p_date_style IN (N'103', N'dd/mm/yyyy') THEN 103
61: WHEN @p_date_style IN (N'4', N'dd.mm.yy') THEN 4
62: WHEN @p_date_style IN (N'104', N'dd.mm.yyyy') THEN 104
63: WHEN @p_date_style IN (N'5', N'dd-mm-yy') THEN 5
64: WHEN @p_date_style IN (N'105', N'dd-mm-yyyy') THEN 105
65: WHEN @p_date_style IN (N'6', N'dd mon yy') THEN 6
66: WHEN @p_date_style IN (N'106', N'dd mon yyyy') THEN 106
67: WHEN @p_date_style IN (N'7', N'mon dd, yy') THEN 7
68: WHEN @p_date_style IN (N'107', N'mon dd, yyyy') THEN 107
69: WHEN @p_date_style IN (N'8', N'24', N'108', N'hh:mi:ss') THEN 108
70: WHEN @p_date_style IN (N'9', N'109', N'mon dd yyyy hh:mi:ss:mmmam') THEN 109
71: WHEN @p_date_style IN (N'10', N'mm-dd-yy') THEN 10
72: WHEN @p_date_style IN (N'110', N'mm-dd-yyyy') THEN 110
73: WHEN @p_date_style IN (N'11', N'yy/mm/dd') THEN 11
74: WHEN @p_date_style IN (N'111', N'yyyy/mm/dd') THEN 111
75: WHEN @p_date_style IN (N'12', N'yymmdd') THEN 12
76: WHEN @p_date_style IN (N'112', N'yyyymmdd') THEN 112
77: WHEN @p_date_style IN (N'13', N'113', N'dd mon yyyy hh:mi:ss:mmm') THEN 113
78: WHEN @p_date_style IN (N'14', N'114', N'hh:mi:ss:mmm') THEN 114
79: WHEN @p_date_style IN (N'20', N'120', N'yyyy-mm-dd hh:mi:ss') THEN 120
80: WHEN @p_date_style IN (N'21', N'25', N'121', N'yyyy-mm-dd hh:mi:ss.mmm') THEN 121
81: WHEN @p_date_style IN (N'22', N'mm/dd/yy hh:mi:ss am') THEN 22
82: WHEN @p_date_style IN (N'23', N'yyyy-mm-dd') THEN 23
83: WHEN @p_date_style IN (N'126', N'yyyy-mm-ddthh:mi:ss.mmm') THEN 126
84: WHEN @p_date_style IN (N'127', N'yyyy-mm-ddthh:mi:ss.mmmz') THEN 127
85: END;
86:
87: IF @style IS NOT NULL
88: SET @return_value = TRY_CONVERT(datetime2(7), @p_date, @style);
89: ELSE
90: SET @return_value = NULL;
91: END;
92:
93: RETURN @return_value;
94: END;
Demo calls
A compact demonstration of all four functions — productive calls along the supported style codes and format strings, plus negative cases guaranteed to return NULL.
1: -- -----------------------------------------------------------------------------
2: -- 1) Klassische style-Codes als String oder als sprechender Format-Bezeichner
3: -- -----------------------------------------------------------------------------
4: SELECT [dbo].[fnConvertDate] (N'24.04.2023', N'104' ) AS d1 -- 2023-04-24
5: ,[dbo].[fnConvertDate] (N'24.04.2023', N'dd.mm.yyyy' ) AS d2 -- identisch: Format-String statt Code
6: ,[dbo].[fnConvertDate] (N'2023-04-24', N'23' ) AS d3
7: ,[dbo].[fnConvertDate] (N'24/04/2023', N'103' ) AS d4
8: ,[dbo].[fnConvertDate] (N'20230424', N'112' ) AS d5;
9:
10: -- -----------------------------------------------------------------------------
11: -- 2) Zeit-Anteil
12: -- -----------------------------------------------------------------------------
13: SELECT [dbo].[fnConvertTime] (N'12:34:15', N'hh:mi:ss' ) AS t1
14: ,[dbo].[fnConvertTime] (N'2023-04-24 12:34:15.1234567', N'yyyy-mm-dd hh:mi:ss.mmm' ) AS t2 -- Code 121: Datum wird verworfen, Zeit-Anteil bleibt
15: ,[dbo].[fnConvertTime] (N'123415', N'hhmmss' ) AS t3
16: ,[dbo].[fnConvertTime] (N'1234151234567', N'hhmmssfffffff' ) AS t4;
17:
18: -- -----------------------------------------------------------------------------
19: -- 3) Datum + Zeit als datetime (Granularitaet ~3,33 ms)
20: -- -----------------------------------------------------------------------------
21: SELECT [dbo].[fnConvertDateTime](N'24.04.2023 12:34:15', N'104' ) AS dt1 -- Style 104 ignoriert die Zeit
22: ,[dbo].[fnConvertDateTime](N'2023-04-24T12:34:15.123', N'126' ) AS dt2
23: ,[dbo].[fnConvertDateTime](N'20230424123415', N'yyyymmddhhmmss' ) AS dt3
24: ,[dbo].[fnConvertDateTime](N'20230424123415123', N'yyyymmddhhmmssfff' ) AS dt4
25: ,[dbo].[fnConvertDateTime](N'20230424_123415123', N'yyyymmdd_hhmissmmm' ) AS dt5;
26:
27: -- -----------------------------------------------------------------------------
28: -- 4) datetime2 mit voller 100-Nanosekunden-Aufloesung
29: -- -----------------------------------------------------------------------------
30: SELECT [dbo].[fnConvertDateTime2](N'2023-04-24T12:34:15.1234567', N'126' ) AS d21
31: ,[dbo].[fnConvertDateTime2](N'24.04.2023', N'dd.mm.yyyy' ) AS d22
32: ,[dbo].[fnConvertDateTime2](N'20230424123415', N'yyyymmddhhmmss' ) AS d23
33: ,[dbo].[fnConvertDateTime2](N'20230424123415123', N'yyyymmddhhmmssfff' ) AS d24
34: ,[dbo].[fnConvertDateTime2](N'202304241234151234567', N'yyyymmddhhmmssfffffff' ) AS d25;
35:
36: -- -----------------------------------------------------------------------------
37: -- 5) Negative Faelle - alle geben NULL zurueck
38: -- -----------------------------------------------------------------------------
39: SELECT [dbo].[fnConvertDate] (N'', N'104' ) AS n1 -- leerer Eingang
40: ,[dbo].[fnConvertDate] (NULL, N'104' ) AS n2 -- NULL
41: ,[dbo].[fnConvertDate] (N'24.04.2023', N'unbekannt' ) AS n3 -- unbekannter Format-String
42: ,[dbo].[fnConvertDate] (N'32.04.2023', N'104' ) AS n4 -- ungueltiger Tag
43: ,[dbo].[fnConvertDate] (N'irgendwas', N'104' ) AS n5; -- nicht parsebar
Postgres Bridge
If you’re porting ETL pipelines from SQL Server to Postgres — or running both engines in parallel — sooner or later you’ll need the counterpart to TRY_CONVERT(date, …, 104). Postgres offers TO_DATE and TO_TIMESTAMP — both take format patterns instead of numeric style codes, which puts their call shape closer to our fnConvertDate interface than to the numeric style-code logic of TRY_CONVERT:
1: -- SQL Server
2: TRY_CONVERT(date, '24.04.2023', 104); -- 2023-04-24
3:
4: -- Postgres
5: SELECT TO_DATE ('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
6: SELECT TO_TIMESTAMP ('24.04.2023 12:34:15', 'DD.MM.YYYY HH24:MI:SS'); -- 2023-04-24 12:34:15
Pattern correspondence for the common formats
SQL Server TRY_CONVERT (style) | Postgres TO_DATE / TO_TIMESTAMP (pattern) | Sample input |
|---|---|---|
104 — dd.mm.yyyy (German) | DD.MM.YYYY | 24.04.2023 |
103 — dd/mm/yyyy (British/French) | DD/MM/YYYY | 24/04/2023 |
101 — mm/dd/yyyy (US) | MM/DD/YYYY | 04/24/2023 |
102 — yyyy.mm.dd (ANSI) | YYYY.MM.DD | 2023.04.24 |
112 — yyyymmdd (ISO) | YYYYMMDD | 20230424 |
23 — yyyy-mm-dd (ISO 8601) | YYYY-MM-DD | 2023-04-24 |
120 — yyyy-mm-dd hh:mi:ss (ODBC) | YYYY-MM-DD HH24:MI:SS | 2023-04-24 12:34:15 |
121 — yyyy-mm-dd hh:mi:ss.mmm (ODBC w/ ms) | YYYY-MM-DD HH24:MI:SS.MS | 2023-04-24 12:34:15.123 |
126 — yyyy-mm-ddThh:mi:ss.mmm (ISO 8601) | YYYY-MM-DD"T"HH24:MI:SS.MS | 2023-04-24T12:34:15.123 |
127 — ISO 8601 with Z time zone | YYYY-MM-DD"T"HH24:MI:SS.MS"Z" | 2023-04-24T12:34:15.123Z |
NULL instead of exception: rolling your own try_to_date
Unlike TRY_CONVERT, TO_DATE does not return NULL on a format mismatch — it raises an invalid_datetime_format exception (SQLSTATE 22007). For bulk imports that need to handle bad records gracefully, wrap the call in a small PL/pgSQL function:
1: CREATE OR REPLACE FUNCTION try_to_date (p_text text, p_pattern text)
2: RETURNS date
3: LANGUAGE plpgsql
4: AS $$
5: BEGIN
6: RETURN TO_DATE(p_text, p_pattern);
7: EXCEPTION
8: WHEN OTHERS THEN
9: RETURN NULL;
10: END;
11: $$;
12:
13: SELECT try_to_date('irgendwas', 'DD.MM.YYYY'); -- NULL statt Exception
14: SELECT try_to_date('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
Watch the locale
Month-name patterns (MON, MONTH, DY, DAY) depend on the session’s lc_time setting. TO_DATE('24-Apr-2023', 'DD-Mon-YYYY') works only when lc_time is on an English locale — on a German locale, Postgres fails to match Apr and raises an invalid_datetime_format exception. In an ETL context, either pin SET lc_time = 'en_US.UTF-8' before the call, or stick to purely numeric patterns (DD, MM, YYYY), which are locale-agnostic.
Summary
When converting text into a date, SQL Server supports only the date formats documented under CAST and CONVERT (Transact-SQL) and assigns a style code to each.
When data arrives from source systems through a text file (CSV, XML, JSON), you have to nail down the exact date format up front. If no style code matches, a user-defined function bridges the gap.
This article presents four such functions, one per target type — date, time, datetime, datetime2. They translate a format string into the appropriate style code and forward the date to TRY_CONVERT.
Take-aways:
TRY_CONVERTwith ayyyystylecode (e.g.104,121,126) is the deterministic first-choice solution for CSV/JSON date imports — avoidyycodes because of their locale sensitivity.- When you know the source format as a readable format string (
dd.mm.yyyy), use thefn-wrappers from this article: more readable in code, no magic numbers. - For new schemas, prefer
datetime2(7)overdatetime: wider value range, more precise fractional seconds, ISO-8601-compliant parsing. - For cross-engine ETL, use the Postgres equivalents
TO_DATE/TO_TIMESTAMPwith format patterns — and keep the patterns locale-agnostic (DD,MM,YYYY).
FAQ
Why does TRY_CONVERT return NULL instead of raising an error?
TRY_CONVERT is the error-tolerant counterpart to CONVERT. When the conversion fails — wrong format, out-of-range value, locale mismatch — you get NULL instead of an exception that aborts the whole pipeline. That’s exactly what makes it useful for ETL bulk imports: bad records land in a reject column instead of blowing up the insert. If you need the hard behaviour (e.g. to catch format bugs early), use plain CONVERT without the TRY_.
What about mixed-language formats within the same column?
A CSV import can easily mix 24.04.2023 (German), 04/24/2023 (US) and 2023-04-24 (ISO). TRY_CONVERT with a single fixed style handles only one of the three. The pragmatic fix: cascade several TRY_CONVERT calls with COALESCE — the first one that doesn’t return NULL wins:
1: COALESCE(
2: TRY_CONVERT(date, @value, 104), -- dd.mm.yyyy
3: TRY_CONVERT(date, @value, 101), -- mm/dd/yyyy
4: TRY_CONVERT(date, @value, 23) -- yyyy-mm-dd (ISO 8601)
5: )
Order the candidates so the most likely source format comes first.
datetime or datetime2 — which one and when?
For new development, datetime2(7) is the right choice: wider value range (down to 0001-01-01), higher precision (100 ns instead of ~3.33 ms), and ISO-8601-compliant parsing. datetime only stays relevant when a legacy schema is nailed to it, or when the tight storage footprint is a concrete optimisation argument. When in doubt: datetime2(7).
Does the conversion result depend on the server’s SET LANGUAGE / SET DATEFORMAT?
Yes — specifically for the style codes that the MS docs flag as „non-deterministic”: style 0/100, 9/109, 13/113 and all yy variants (1, 2, 3, 4, 5, 6, 7, 10, 11, 12, 22) parse month names or two-digit years context-dependently. Apr is recognised as English depending on the server locale — and not on a German-configured server. For deterministic ETL pipelines, always prefer the yyyy variants (e.g. 104 over 4) and the ISO codes (23, 121, 126).
Postgres equivalent for TRY_CONVERT(date, …, 104)?
TO_DATE('24.04.2023', 'DD.MM.YYYY') — see the Postgres Bridge section for the full pattern-correspondence table and the try_to_date wrapper that mirrors the NULL-instead-of-exception behaviour of TRY_CONVERT.
Related Posts
ETL context:
TRY_CONVERT for other data types: