Content
Overview
Probably the most challenging conversion is the conversion of a date and/or time, which is supplied as text, into a value of type date, time, datetime or datetime2.
SQL Server provides the following data types, among others, for storing a date and/or time:
- date
- time
- datetime
- datetime2
To convert a date supplied as text, the CONVERT and TRY_CONVERT standard functions are available, which identify the respective date format of the date supplied via the style parameter. For example, the style parameter 104 specifies that the date to be converted in the expression parameter is a German date with a four-digit year in accordance with the formatting string dd.mm.yyyy.
1: TRY_CONVERT(date, ’24.04.2023’, 104) -- > 24.04.2023Microsoft describes the supported date formats in the online documentation under CAST and CONVERT (Transact-SQL). Each date format has a style code, which is passed as the third parameter to the TRY_CONVERT but also CONVERT function and thus controls the interpretation of the date. You find the codes in the sytle codes section.
In addition, SQL Server knows other format strings or the definition of format identifiers that can be interpreted by the FORMAT function to format a date or number in a text according to the specified format string. For example, the date 24.04.2023 12:34:15.123 is translated into the text 20230418123415123 according to the format string yyyyMMddhhmmssfff.
1: FORMAT(TRY_CONVERT(datetime, N'24.04.2023 12:34:15.123', 104), 'yyyyMMddhhmmssfff')
2: -- > 20230418123415123Further information on user-defined format strings can be found in the section Format Identifier.
Conversely, SQL Server unfortunately does not provide us with a conversion function that interprets a date/time that exists as text according to a format string – as described in the previous section. User-defined stored functions must therefore be developed to convert a date that is passed as text according to a format string. This article presents four user-defined stored functions for converting an input value into the data types date, time, datetime or datetime2:
The code for these functions can be found in the section User-defined functions for converting a date.
The return result depends on the target data type, as this determines the value range of a date and, in the case of time specifications, the accuracy. While SQL Server supports a value range from 01.01.1753 to 31.12.9999 for the data types date and datetime and the data type datetime2 supports a value range from 01.01.0001 to 31.12.9999, the Oracle and MySQL database systems, for example, support the value range 01.01.0000 to 31.12.9999 with the data types DATE, DATETIME and TIMESTAMP. If the date also contains a time, the accuracy in fractions of a second with which a time is to be saved must be taken into account. The Oracle data types DATETIME and TIMESTAMP generally support the storage of fractions of a second with 7 decimal places. In SQL Server, the datatypes datetime2 and time support the storage of 7 decimal places, while the datatype datetime only supports 3 decimal places.
Style Codes
| Style | Formatstrings | |||
| yy | yyyy | Country/Description | Formatstring | |
| – | 0 oder 100 | Default for datetime and smalldatetime | mon dd yyyy hh:miAM | |
| 1 | 101 | United States | 1 = | mm/dd/yy |
| 101 = | mm/dd/yyyy | |||
| 2 | 102 | ANSI | 2 = | yy.mm.dd |
| 102 = | yyyy.mm.dd | |||
| 3 | 103 | England/Frankreich | 3 = | dd/mm/yy |
| 103 = | dd/mm/yyyy | |||
| 4 | 104 | Deutschland | 4 = | dd.mm.yy |
| 104 = | dd.mm.yyyy | |||
| 10 | 110 | United States | 10 = | mm-dd-yy |
| 110 = | mm-dd-yyyy | |||
| 12 | 112 | ISO | 12 = | yymmdd |
| 112 = | yyyymmdd | |||
| 13 | 113 | Europe (Default) including time with milliseconds | 13 = | dd mon yyyy hh:mi:ss:mmm |
| 113 = | (24 Stunden) | |||
Format Identifier
Microsoft provides a set of format identifiers for the textual representation of a date. An excerpt of the most important identifiers can be found in the following table:
| Format Identifyer | Description |
| d | The day of the month, from 1 to 31. |
| dd | The day of the month, from 01 to 31. |
| ddd | The abbreviated name of the day of the week. |
| dddd | The full name of the day of the week. |
| f | The tenth of a second in a date and time value. |
| ff | The hundredth of a second in a date and time value. |
| fff | The milliseconds in a date and time value. |
| h | The hour, from 1 to 12 (12-hour format). |
| hh | The hour, from 01 to 12 (12-hour format). |
| H | The hour, from 0 to 23 (24-hour format). |
| HH | The hour, from 00 to 23 (24-hour format). |
| m | The minute, from 0 to 59. |
| mm | The minute, from 00 to 59. |
| M | The month, from 1 to 12. |
| MM | The month, from 01 to 12. |
| s | The second, from 0 to 59. |
| ss | The second, from 00 to 59. |
| yy | The year, from 00 to 99. |
| yyyy | The year as a four-digit number. |
User-defined Functions
The implementations of the conversion functions expect a date in the p_Date parameter, which is transferred as text. The p_DateStyle parameter specifies a user-defined format string or one of the style parameters mentioned above. The supported format strings and style codes can be found in the code. The expected format strings do not correspond exactly to the format identifiers as specified in the section Format Identifier. As mentioned above, Microsoft is inconsistent in the definition of format strings and format identifiers. The user-defined stored functions deal with this fact pragmatically and do not interpret format strings, but translate them into the corresponding style parameter or the formatting string is specifically queried and implemented. The format string is queried using Case Insensitive.
The following code examples do not show all translations of the format strings in the associated style parameters. Missing translations are replaced by the character string […]. When using these functions, this character string must be replaced by the corresponding translations.
Declaration
The declaration of the user-defined stored functions can generally be specified as follows:
Description
Converts a transferred input value into the target data type date, time, datetime or datetime2. If the input value cannot be converted, NULL is returned. The transferred value is treated as Case Insensitive.
Syntax
1: [dbo].[fnConvertDate] (@p_Date AS nvarchar(50), @p_DateStyle nvarchar(50))Arguments
- p_Date
Specifies the input value to be converted. - p_DateStyle
Specifies a format string according to which a date is passed in p_Date, or a style code. The format string or the style parameter control the conversion of the date. The supported format strings and style codes can be found in the code.
Return
Returns the converted value as a value of type date, time, datetime or datetime2 if the conversion is successful. If the input value cannot be converted, NULL is returned. If NULL or an empty string is passed, the functions returns NULL.
[dbo].[fnConvertDate]
1: CREATE FUNCTION [dbo].[fnConvertDate] (@p_Date AS nvarchar(50), @p_DateStyle nvarchar(50))
2: RETURNS date
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS date;
9:
10: SET @p_DateStyle = LOWER(@p_DateStyle);
11:
12: -- --------------------------------------------------------------------------------
13: -- Workload
14: -- --------------------------------------------------------------------------------
15: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
16: BEGIN
17: SET @returnValue = NULL;
18: END
19: ELSE IF @p_DateStyle IN (
20: N'yyyy-mm-dd' , N'23'
21: ,N'mon dd yyyy hh:miam' , N'100'
22: ,N'mm/dd/yyyy' , N'101'
23: ,N'yyyy.mm.dd' , N'102'
24: ,[...]
25: )
26: BEGIN
27: SET @returnValue = CASE
28: WHEN @p_DateStyle IN (N'yyyy-mm-dd' , N'23' )
29: THEN TRY_CONVERT(date, @p_Date, 23)
30: WHEN @p_DateStyle IN (N'mon dd yyyy hh:miam', N'100')
31: THEN TRY_CONVERT(date, @p_Date, 100)
32: WHEN @p_DateStyle IN (N'mm/dd/yyyy' , N'101')
33: THEN TRY_CONVERT(date, @p_Date, 101)
34: WHEN @p_DateStyle IN (N'yyyy.mm.dd' , N'102')
35: THEN TRY_CONVERT(date, @p_Date, 102)
36: WHEN @p_DateStyle IN (N'dd/mm/yyyy' , N'103')
37: THEN TRY_CONVERT(date, @p_Date, 103)
38: [...]
39: END;
40: END
41: ELSE
42: BEGIN
43: SET @returnValue = NULL;
44: END;
45:
46: RETURN @returnValue;
47: END;
[dbo].[fnConvertTime]
1: CREATE FUNCTION [dbo].[fnConvertTime](@p_Time AS nvarchar(50), @p_TimeStyle nvarchar(50))
2: RETURNS time(7)
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS time;
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_TimeStyle = LOWER(@p_TimeStyle)
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Time, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_TimeStyle IN ( N'yyyymmddhhmmss'
23: ,N'yyyymmddhhmmssf'
24: ,N'yyyymmddhhmmssff'
25: ,[...]
26: ,N'yyyymmddhhmmssfffffff')
27: BEGIN
28: SET @returnValue = TRY_CONVERT( time(7) -- 202304181011121234567
29: ,SUBSTRING(@p_Time, 1, 4)+'-'+ -- 2023
30: SUBSTRING(@p_Time, 5, 2)+'-'+ -- 04
31: SUBSTRING(@p_Time, 7, 2)+' '+ -- 18
32: SUBSTRING(@p_Time, 9, 2)+':'+ -- 10
33: SUBSTRING(@p_Time,11, 2)+':'+ -- 11
34: SUBSTRING(@p_Time,13, 2)+'.'+ -- 12
35: SUBSTRING(@p_Time,15, 7) -- 1234567
36: ,120
37: )
38: END
39: ELSE IF @p_TimeStyle IN ( N'hhmmss'
40: ,N'hhmmssf'
41: ,N'hhmmssff'
42: ,N'hhmmssfff'
43: ,[...]
44: ,N'hhmmssfffffff')
45: BEGIN
46: SET @returnValue = TRY_CONVERT( time(7) -- 1011121234567
47: ,SUBSTRING(@p_Time, 1, 2)+':'+ -- 10
48: SUBSTRING(@p_Time, 3, 2)+':'+ -- 11
49: SUBSTRING(@p_Time, 5, 2)+'.'+ -- 12
50: SUBSTRING(@p_Time, 7, 7) -- 1234567
51: ,120
52: )
53: END
54: ELSE IF @p_TimeStyle IN (
55: N'mon dd yyyy hh:miam' , N'100'
56: ,N'mm/dd/yyyy' , N'101'
57: ,N'yyyy.mm.dd' , N'102'
58: ,N'dd/mm/yyyy' , N'103'
59: ,[...]
60: )
61: BEGIN
62: SET @returnValue = CASE
63: WHEN @p_TimeStyle IN (N'yyyy.mm.dd' , N'102')
64: THEN TRY_CONVERT(time(7), @p_Time, 102)
65: WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss' , N'120')
66: THEN TRY_CONVERT(time(7), @p_Time, 120)
67: WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss.mmm' , N'121')
68: THEN TRY_CONVERT(time(7), @p_Time, 121)
69: WHEN @p_TimeStyle IN (N'yyyy-mm-ddthh:mi:ss.mmm' , N'126')
70: THEN TRY_CONVERT(time(7), @p_Time, 126)
71: [...]
72: END;
73: END
74: ELSE
75: BEGIN
76: SET @returnValue = NULL;
77: END;
78:
79: RETURN @returnValue;
80: END;
[dbo].[fnConvertDateTime]
1: CREATE FUNCTION [dbo].[fnConvertDateTime]
(
@p_date AS nvarchar(50)
,@p_dateStyle AS nvarchar(50)
)
2: RETURNS datetime
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS datetime;
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_dateStyle = LOWER(@p_dateStyle);
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_dateStyle IN ( N'yyyymmddhhmmss'
, N'yyyymmddhhmmssf'
, N'yyyymmddhhmmssff'
, N'yyyymmddhhmmssfff'
)
23: BEGIN
24: SET @returnValue = TRY_CONVERT(datetime2 -- 20230418101112123
25: ,SUBSTRING(@p_date, 1, 4)+'-'+ -- 2023
26: SUBSTRING(@p_date, 5, 2)+'-'+ -- 04
27: SUBSTRING(@p_date, 7, 2)+' '+ -- 18
28: SUBSTRING(@p_date, 9, 2)+':'+ -- 10
29: SUBSTRING(@p_date,11, 2)+':'+ -- 11
30: SUBSTRING(@p_date,13, 2)+'.'+ -- 12
31: SUBSTRING(@p_date,15, 3) -- 123
32: ,120
33: )
34: END
35: ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'
36: BEGIN
37: SET @returnValue = TRY_CONVERT(datetime
38: ,SUBSTRING(@p_date, 1, 4)+'-'+
39: SUBSTRING(@p_date, 5, 2)+'-'+
40: SUBSTRING(@p_date, 7, 2)+' '+
41: SUBSTRING(@p_date,10, 2)+':'+
42: SUBSTRING(@p_date,12, 2)+':'+
43: SUBSTRING(@p_date,14, 2)+'.'+
44: SUBSTRING(@p_date,16, 3)
45: ,121
46: )
47: END
48: ELSE IF @p_dateStyle IN (
49: N'yyyy-mm-dd' , N'23'
50: ,N'mon dd yyyy hh:miam', N'100'
51: ,N'mm/dd/yyyy' , N'101'
52: ,N'yyyy.mm.dd' , N'102'
53: ,[...]
54: )
55: BEGIN
56: SET @returnValue = CASE
57: WHEN @p_dateStyle IN (N'yyyy-mm-dd' , N'23' )
58: THEN TRY_CONVERT(datetime, @p_date, 23)
59: WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100')
60: THEN TRY_CONVERT(datetime, @p_date, 100)
61: WHEN @p_dateStyle IN (N'mm/dd/yyyy' , N'101')
62: THEN TRY_CONVERT(datetime, @p_date, 101)
63: WHEN @p_dateStyle IN (N'yyyy.mm.dd' , N'102')
64: THEN TRY_CONVERT(datetime, @p_date, 102)
65: [...]
66: END;
67: END
68: ELSE
69: BEGIN
70: SET @returnValue = NULL;
71: END;
72:
73: RETURN @returnValue;
74: END;
[dbo].[fnConvertDateTime2]
1: CREATE FUNCTION [dbo].[fnConvertDateTime2] (@p_date AS nvarchar(50), @p_dateStyle nvarchar(50))
2: RETURNS datetime2(7)
3: AS
4: BEGIN
5: -- --------------------------------------------------------------------------------
6: -- Declare variables
7: -- --------------------------------------------------------------------------------
8: DECLARE @returnValue AS datetime2(7);
9:
10: -- --------------------------------------------------------------------------------
11: -- Initialize variables
12: -- --------------------------------------------------------------------------------
13: SET @p_dateStyle = LOWER(@p_dateStyle)
14:
15: -- --------------------------------------------------------------------------------
16: -- Workload
17: -- --------------------------------------------------------------------------------
18: IF [dbo].[fnIsNullOrEmpty](@p_Date, 1) = 1
19: BEGIN
20: SET @returnValue = NULL;
21: END
22: ELSE IF @p_dateStyle IN ( N'yyyymmddhhmmss'
23: ,N'yyyymmddhhmmssf'
24: ,N'yyyymmddhhmmssff'
25: ,[...]
26: ,N'yyyymmddhhmmssfffffff')
27: BEGIN
28: SET @returnValue = TRY_CONVERT( datetime2 -- 202304181011121234567
29: ,SUBSTRING(@p_date, 1, 4)+'-'+ -- 2023
30: SUBSTRING(@p_date, 5, 2)+'-'+ -- 04
31: SUBSTRING(@p_date, 7, 2)+' '+ -- 18
32: SUBSTRING(@p_date, 9, 2)+':'+ -- 10
33: SUBSTRING(@p_date,11, 2)+':'+ -- 11
34: SUBSTRING(@p_date,13, 2)+'.'+ -- 12
35: SUBSTRING(@p_date,15, 7) -- 1234567
36: ,120
37: )
38: END
39: ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'
40: BEGIN
41: SET @returnValue = TRY_CONVERT( datetime2(7)
42: ,SUBSTRING(@p_date, 1, 4)+'-'+
43: SUBSTRING(@p_date, 5, 2)+'-'+
44: SUBSTRING(@p_date, 7, 2)+' '+
45: SUBSTRING(@p_date,10, 2)+':'+
46: SUBSTRING(@p_date,12, 2)+':'+
47: SUBSTRING(@p_date,14, 2)+'.'+
48: SUBSTRING(@p_date,16, 3)
49: ,121
50: )
51: END
52: ELSE IF @p_dateStyle IN (
53: N'mon dd yyyy hh:miam' , N'100'
54: ,N'mm/dd/yyyy' , N'101'
55: ,N'yyyy.mm.dd' , N'102'
56: ,N'dd/mm/yyyy' , N'103'
57: ,[...]
58: )
59: BEGIN
60: SET @returnValue = CASE
61: WHEN @p_dateStyle IN (N'yyyy.mm.dd' , N'102')
62: THEN TRY_CONVERT(datetime2(7), @p_date, 102)
63: WHEN @p_dateStyle IN (N'yyyy/mm/dd' , N'111')
64: THEN TRY_CONVERT(datetime2(7), @p_date, 111)
65: WHEN @p_dateStyle IN (N'yyyymmdd' , N'112')
66: THEN TRY_CONVERT(datetime2(7), @p_date, 112)
67: WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100')
68: THEN TRY_CONVERT(datetime2(7), @p_date, 100)
69: END;
70: END
71: ELSE
72: BEGIN
73: SET @returnValue = NULL;
74: END;
75:
76: RETURN @returnValue;
77: END;
Summary
When converting a text into a date, SQL Server only supports the date formats documented under CAST and CONVERT (Transact-SQL) and assigns a style code to the formats listed there.
If data is supplied from source systems via a text file (CSV, XML, JSON), it is essential to clarify the format in which a date is supplied. If there is no style code for a date format available, a user-defined function must be developed to carry out the conversion.
This article presents four functions that convert a date into the following data types: date, time, datetime or datetime2. The functions translate a format string to be passed into a style code and convert the date also passed into the respective target data type.
Related Posts
- Data quality in an ETL process
- Datenqualität // Grundlagen der Typ-Konvertierung mit T-SQL
- TRY_CONVERT // Converting data to bigint, int, smallint, tinyint
- TRY_CONVERT // Converting data to decimal or numeric
- TRY_CONVERT // Converting data to money, smallmoney
- TRY_CONVERT // Converting data to float, real
- TRY_CONVERT // Converting data to bit
- TRY_CONVERT // Converting data to date, datetime, datetime2, time