If you’ve ever imported a CSV column with mixed date formats into a datetime column, you know: Type conversion is where bad date values either surface or slip silently into the pipeline. SQL Server leaves you alone with style codes the moment the format strays from the documented ones — TRY_CONVERT covers the documented style formats, anything else needs a parsing strategy of your own.
What you’ll take away:
- When
TRY_CONVERTwith astylecode is enough — and when you need a custom function. - The most important
stylecodes fordate/datetime/datetime2/timeat a glance. - Four reusable UDFs
fnConvertDate,fnConvertTime,fnConvertDateTime,fnConvertDateTime2as an implementation example — addressed via readable format names (dd.mm.yyyy) instead of numericstylecodes. - The Postgres bridge (
TO_DATE,TO_TIMESTAMP) as a conceptual counterpart for multi-engine ETL — and where its liberal parsing becomes a trap. - A FAQ block covering the most common CSV/JSON import pitfalls.
Prerequisites: SQL Server 2012+ (TRY_CONVERT requires compatibility level 110+), 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 One of the trickiest conversions in day-to-day ETL is turning a date or time supplied as text into a value of type date, time, datetime or datetime2.
SQL Server offers, among others, the following data types for storing dates and times:
datetimedatetimedatetime2
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) -- 2023-04-24
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 CONVERT and controls how the date is parsed. The codes are listed in the Style Codes section.
The style parameter is optional. Without it, the interpretation can depend on the session context (SET LANGUAGE, SET DATEFORMAT) — not reproducible for ETL pipelines, which is why this article works with explicit style codes throughout. There is also TRY_PARSE, a .NET-based parser with a culture parameter. It is considerably slower and therefore stays out of scope for bulk imports.
SQL Server also has a second set of format identifiers, which the FORMAT function interprets to render a date or a number as text according to a given format string. For example, the date 24.04.2023 12:34:15.123 is rendered as 20230424123415123 by the format string yyyyMMddHHmmssfff (HH is the 24-hour format — lowercase hh would be the 12-hour format and would turn 10 p.m. into a 10).
1: FORMAT(TRY_CONVERT(datetime, N'24.04.2023 12:34:15.123', 104), 'yyyyMMddHHmmssfff')
2: -- 20230424123415123
See the Format Identifiers section for more on these format identifiers.
Conversely, SQL Server lacks a conversion function that parses a date supplied as text according to such a format string. If a date arrives in a format with no matching style code, you have to build the conversion yourself. This article presents four user-defined functions for that job, converting an input value to date, time, datetime or datetime2:
The code of the four functions 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. The ISO 8601 literal format parses independently of session settings.
For new development, Microsoft recommends datetime2 over datetime. datetime2 has a wider value range and more precise fractional seconds, and the ISO 8601 literal format (yyyy-MM-ddTHH:mm:ss[.fffffff], style 126) is interpreted independently of SET LANGUAGE and SET DATEFORMAT. The precision is configurable (datetime2(0) to datetime2(7)) and should match the actual business requirement. A detailed comparison with Oracle (DATE, TIMESTAMP) and MySQL (DATETIME, TIMESTAMP) is deliberately out of scope here. Value ranges and precisions differ substantially across RDBMS vendors and belong in the respective vendor manual.
Decision guide:
Multiple formats in one column → cascaded TRY_CONVERT attempts with COALESCE (see FAQ).
The source format matches a documented style code → TRY_CONVERT with a deterministic style (four-digit numeric such as 104/112, or ISO such as 23/121/126).
The format is known as a format string but has no matching style code → custom function (below) or a transformation step in staging.
Style Codes
A selection of the codes most common in day-to-day ETL — the full list lives in the MS docs under CAST and CONVERT (Transact-SQL):
| Style | Country / description | Format (yy) | Format (yyyy) | Typical target type |
|---|---|---|---|---|
-/0/100 | Default for datetime & smalldatetime | – | mon dd yyyy hh:miAM | datetime |
1/101 | United States | mm/dd/yy | mm/dd/yyyy | date |
2/102 | ANSI | yy.mm.dd | yyyy.mm.dd | date |
3/103 | British / French | dd/mm/yy | dd/mm/yyyy | date |
4/104 | German | dd.mm.yy | dd.mm.yyyy | date |
10/110 | United States | mm-dd-yy | mm-dd-yyyy | date |
12/112 | ISO | yymmdd | yyyymmdd | date |
13/113 | European default (with ms, 24h) | dd mon yyyy hh:mi:ss:mmm | dd mon yyyy hh:mi:ss:mmm | datetime/datetime2 |
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 (p_time for the time variant). The p_date_style parameter accepts either a user-defined format string (dd.mm.yyyy, yyyy-mm-dd, …) or one of the numeric style codes mentioned above. The expected format strings do not exactly match the format identifiers from the Format Identifiers section — Microsoft defines the identifiers for the style formats and for FORMAT differently. In the style formats, mm stands for the month and mi for the minute, while with FORMAT, MM is the month and mm the minute. The functions take a pragmatic approach: They don’t interpret the format string character by character: they translate it into the matching style code, or handle it as an explicitly implemented special case. Format strings are matched independently of upper and lower case.
A note on scope: The four functions are convenience wrappers for pipelines where the source format is configured as a readable format string — not a general recommendation to route every text conversion through a scalar UDF. Scalar UDFs execute row by row and can become the bottleneck on large data volumes. Whether the optimizer can inline such a function (scalar UDF inlining, SQL Server 2019+) depends on the function and the compatibility level, and deserves a separate check for large ETL runs. There, the style-code translation is often better placed directly as an expression in the SELECT, or in an upstream staging step.
Declaration
The declaration of the four functions follows a shared pattern:
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 is normalised with LOWER() before evaluation and is therefore independent of upper and lower case.
Syntax
1: [dbo].[fnConvertDate] (@p_date AS nvarchar(50), @p_date_style nvarchar(50))
Arguments
p_date— the input value to convert.p_date_style— a format string describing how the date inp_dateis laid out, or astylecode. The format string orstylecode controls the conversion. 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 — typical calls along the supported style codes and format strings, plus negative cases that return NULL with this implementation.
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 the fnConvertDate interface from this article 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 unparseable input — it raises an exception: invalid_datetime_format (SQLSTATE 22007) for unreadable characters, datetime_field_overflow (22008) for values outside the calendar such as '32.04.2023'. For bulk imports that need to tolerate bad records, wrap the call in a small PL/pgSQL function. It is a safety net for parse errors, not yet a format validation (more on that in a moment). It deliberately catches only the two date error classes, because a blanket WHEN OTHERS would also swallow errors that have nothing to do with the input:
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 invalid_datetime_format OR datetime_field_overflow THEN
9: RETURN NULL;
10: END;
11: $$;
12:
13: SELECT try_to_date('irgendwas', 'DD.MM.YYYY'); -- NULL instead of an exception
14: SELECT try_to_date('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
The exception wrapper alone is not strict validation yet. TO_DATE parses deliberately liberally: Separators don’t have to match the pattern exactly (24/04/2023 also passes against DD.MM.YYYY), and shorter digit groups are taken literally. TO_DATE('24.4.23', 'DD.MM.YYYY') therefore raises no exception — it silently returns the year 23 (0023-04-24). The nearest-to-2020 adjustment described in the Postgres docs only applies to year patterns with fewer than four digits. With YYYY, the value counts literally. The FX modifier (“fixed format”) doesn’t close this gap either: It stops extra whitespace from being skipped, but still accepts foreign single separators (TO_DATE('24/04/2023', 'FXDD.MM.YYYY') parses just fine) and shortened digit groups (behaviour verified on PostgreSQL 17).
A try_to_date that only catches exceptions therefore accepts such inputs as valid — exactly the kind of error a data quality pipeline is supposed to find. The check becomes strict through a round trip: The result is formatted back with the same pattern and must reproduce the input exactly. That enforces the canonical representation of the pattern (two-digit days and months, four-digit year, exact separators) and comes very close to the style-code semantics of TRY_CONVERT:
1: CREATE OR REPLACE FUNCTION try_to_date_strict (p_text text, p_pattern text)
2: RETURNS date
3: LANGUAGE plpgsql
4: AS $$
5: DECLARE
6: l_result date;
7: BEGIN
8: l_result := TO_DATE(p_text, p_pattern);
9:
10: -- round trip: only the canonical representation of the pattern counts as valid
11: IF TO_CHAR(l_result, p_pattern) <> p_text THEN
12: RETURN NULL;
13: END IF;
14:
15: RETURN l_result;
16: EXCEPTION
17: WHEN invalid_datetime_format OR datetime_field_overflow THEN
18: RETURN NULL;
19: END;
20: $$;
21:
22: SELECT try_to_date_strict('24.04.2023', 'DD.MM.YYYY'); -- 2023-04-24
23: SELECT try_to_date_strict('24.4.23', 'DD.MM.YYYY'); -- NULL instead of 0023-04-24
The round trip carries for canonical numeric patterns like DD.MM.YYYY. For patterns with month names or the FM modifier it is no universal validator: There, the TO_CHAR output deviates from the original spelling even for accepted inputs — '24-OCT-2023' parses, but is formatted back as 24-Oct-2023 and would fail the comparison.
Watch the locale
Month-name patterns (MON, MONTH, DY, DAY) parse English names only in Postgres. The TM modifier, which produces localised month and day names with TO_CHAR, is ignored by TO_DATE/TO_TIMESTAMP — the session’s lc_time setting has no influence on parsing. TO_DATE('24-Oct-2023', 'DD-Mon-YYYY') therefore works on a German locale too, while the German '24-Okt-2023' always fails with invalid_datetime_format. That is the reverse of SQL Server, where month-name styles parse differently depending on SET LANGUAGE (see FAQ). For reproducible ETL pipelines, the same advice holds in both engines: use purely numeric patterns (DD, MM, YYYY).
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 of them.
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, you need your own parsing or transformation logic — as an expression in staging, in the ETL tool, or as a user-defined function like the ones in this article.
This article presents four such functions, one per target type — date, time, datetime, datetime2. They translate the supplied format string into the appropriate style code and convert the supplied date into the respective target type.
Take-aways:
TRY_CONVERTwith an explicit deterministicstylecode — four-digit numeric such as104/112, or ISO such as121/126— is the first choice for CSV/JSON date imports.yycodes and month-name styles (106,107,109,113) remain context-dependent.- When you know the source format as a readable format string (
dd.mm.yyyy), use thefnwrappers from this article. They are readable and get by without magic numbers. - For new use cases,
datetime2is usually the better choice overdatetime— with a precision (datetime2(0)todatetime2(7)) that matches the business requirement.datetime2parses the ISO 8601 literal format independently of session settings. - For cross-engine ETL,
TO_DATE/TO_TIMESTAMPwith format patterns take over this role conceptually — semantically identical toTRY_CONVERTthey are not: They parse liberally (even with theFXmodifier), and only the round trip provides a strict check (see Postgres Bridge). Purely numeric patterns (DD,MM,YYYY) remain the robust choice.
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, a language-dependent style on a misconfigured session — 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 is usually the right choice: a wider value range (down to 0001-01-01), up to 100 ns precision instead of ~3.33 ms, and the ISO 8601 literal format (yyyy-MM-ddTHH:mm:ss[.fffffff]) parses independently of SET LANGUAGE and SET DATEFORMAT. The precision belongs to the business requirement: datetime2(0) is enough for second resolution, datetime2(3) for milliseconds, datetime2(7) only when 100-ns resolution is genuinely needed. datetime stays relevant when a legacy schema is nailed to it.
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”: 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. '24 Dez 2023' parses with style 106 under SET LANGUAGE German, while the same call returns NULL under us_english. For deterministic ETL pipelines, prefer the four-digit numeric variants (e.g. 104 over 4) and the ISO codes (23, 121, 126). Four digits alone are not enough: Month-name styles such as 106/113 remain language-dependent despite the yyyy year.
Postgres equivalent for TRY_CONVERT(date, …, 104)?
TO_DATE('24.04.2023', 'DD.MM.YYYY') — see the Postgres Bridge section for the pattern-correspondence table and the try_to_date wrapper that mirrors the NULL-instead-of-exception behaviour of TRY_CONVERT. It is not an exact 1:1 counterpart: TO_DATE parses liberally (separators and digit widths are not strict). For strict validation, use the round-trip variant try_to_date_strict from the same section.
Related Posts
ETL context:
- Data quality in an ETL process
- ETL vs. ELT — How to Tell Which Pattern You Actually Built — the macro view: ETL vs. ELT as an architecture decision, not the order of the letters.
- Design Pattern // Safe Type Conversion with T-SQL — the overarching pattern (materialization + error identification) that embeds these
TRY_CONVERTbuilding blocks.
TRY_CONVERT for other data types:
- Data quality in SQL Server // TRY_CONVERT for decimal and numeric done safely
- Data quality in SQL Server // TRY_CONVERT for bigint, int, smallint and tinyint done safely
- Data quality in SQL Server // TRY_CONVERT for money and smallmoney done safely
- Data quality in SQL Server // TRY_CONVERT for float and real done safely
- Data quality in SQL Server // TRY_CONVERT for bit done safely — converting yes/no values
Fundamentals:
- Data Quality // Type Conversion Basics with T-SQL — CAST, CONVERT, TRY_CAST and TRY_CONVERT compared.