{"id":1733,"date":"2024-03-09T13:00:16","date_gmt":"2024-03-09T12:00:16","guid":{"rendered":"https:\/\/staging.sql.marcus-belz.de\/?p=1733"},"modified":"2026-05-08T14:08:37","modified_gmt":"2026-05-08T12:08:37","slug":"dataquality-converting-data-to-date-datetime-datetime2-time","status":"publish","type":"post","link":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en","title":{"rendered":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time"},"content":{"rendered":"\n<h1 class=\"wp-block-heading\">Content<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#AnchorOverview\">Overview<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorStyleCode\">style-Codes<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorFormatStrings\">Format Identifier<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorUserDefinedFunctions\">User-defined Functions<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorSummary\">Summary<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorRelatedPosts\">Related Posts<\/a><\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorOverview\">Overview<\/h1>\n\n\n\n<p>Probably the most challenging conversion is the conversion of a date and\/or time, which is supplied as text, into a value of type <em>date<\/em>, <em>time<\/em>, <em>datetime<\/em> or <em>datetime2<\/em>.<\/p>\n\n\n\n<p><em>SQL Server<\/em> provides the following data types, among others, for storing a date and\/or time:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>date<\/em><\/li>\n\n\n\n<li><em>time<\/em><\/li>\n\n\n\n<li><em>datetime<\/em><\/li>\n\n\n\n<li><em>datetime2<\/em><\/li>\n<\/ul>\n\n\n\n<p>To convert a date supplied as text, the <strong>CONVERT<\/strong> and <strong><a href=\"https:\/\/learn.microsoft.com\/de-de\/sql\/t-sql\/functions\/try-convert-transact-sql?view=sql-server-ver16\">TRY_CONVERT<\/a><\/strong> standard functions are available, which identify the respective date format of the date supplied via the style parameter. For example, the <em>style <\/em>parameter <em>10<\/em>4 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 <em>dd.mm.yyyy<\/em>.<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code>&nbsp;1: TRY_CONVERT(date, \u201924.04.2023\u2019, 104) -- &gt; 24.04.2023<\/code><\/pre>\n\n\n\n<p>Microsoft describes the supported date formats in the online documentation under <em><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles\">CAST\u00a0and\u00a0CONVERT\u00a0(Transact-SQL)<\/a><\/em>. Each date format has a style code, which is passed as the third parameter to the <strong>TRY_CONVERT<\/strong> but also <strong><em>CONVERT<\/em><\/strong> function and thus controls the interpretation of the date. You find the codes in the <a href=\"#style-Code\">sytle codes<\/a> section.<\/p>\n\n\n\n<p>In addition, <em>SQL Server<\/em> knows other format strings or the definition of format identifiers that can be interpreted by the <strong>FORMAT <\/strong>function to format a date or number in a text according to the specified format string. For example, the date <em>24.04.2023 12:34:15.123<\/em> is translated into the text <em>20230418123415123 <\/em>according to the format string <em>yyyyMMddhhmmssfff<\/em>.<\/p>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code>&nbsp;1: FORMAT(TRY_CONVERT(datetime, N'24.04.2023 12:34:15.123', 104), 'yyyyMMddhhmmssfff')\n&nbsp;2: -- &gt; 20230418123415123<\/code><\/pre>\n\n\n\n<p>Further information on user-defined format strings can be found in the section <a href=\"#AnchorFormatStrings\">Format Identifier<\/a>.<\/p>\n\n\n\n<p>Conversely, <em>SQL Server <\/em>unfortunately does not provide us with a conversion function that interprets a date\/time that exists as text according to a format string &#8211; 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 <em>date<\/em>, <em>time<\/em>, <em>datetime<\/em> or <em>datetime2<\/em>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"#AnchorfnConvertDate\">[dbo].[fnConvertDate]<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorfnConvertTime\">[dbo].[fnConvertTime]<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorfnConvertDateTime\">[dbo].[fnConvertDateTime]<\/a><\/li>\n\n\n\n<li><a href=\"#AnchorfnConvertDateTime2\">[dbo].[fnConvertDateTime2]<\/a><\/li>\n<\/ul>\n\n\n\n<p>The code for these functions can be found in the section <a href=\"#AnchorUserDefinedFunctions\">User-defined functions<\/a> for converting a date.<\/p>\n\n\n\n<p>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 <em>SQL Server <\/em>supports a value range from <em>01.01.1753<\/em> to <em>31.12.9999<\/em> for the data types date and datetime and the data type <em>datetime2<\/em> supports a value range from <em>01.01.0001<\/em> to <em>31.12.9999<\/em>, the <em>Oracle <\/em>and <em>MySQL <\/em>database systems, for example, support the value range <em>01.01.0000<\/em> to <em>31.12.9999<\/em> with the data types <em>DATE<\/em>, <em>DATETIME<\/em> and <em>TIMESTAMP<\/em>. 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 <em>DATETIME <\/em>and <em>TIMESTAMP <\/em>generally support the storage of fractions of a second with 7 decimal places. In SQL Server, the datatypes <em>datetime2 <\/em>and <em>time <\/em>support the storage of 7 decimal places, while the datatype <em>datetime <\/em>only supports 3 decimal places.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorStyleCode\">Style Codes<\/h1>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td class=\"has-text-align-left\" data-align=\"left\" colspan=\"2\"><strong>Style<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" colspan=\"3\"><strong>Formatstrings<\/strong><\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>yy<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\"><strong>yyyy<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\"><strong>Country\/Description<\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" colspan=\"2\"><strong><strong>Formatstring<\/strong><\/strong><\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>&#8211;<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\"><strong>0<\/strong> oder <strong>100<\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">Default for <em>datetime<\/em> and <em>smalldatetime<\/em><\/td><td class=\"has-text-align-left\" data-align=\"left\">&nbsp;<\/td><td class=\"has-text-align-left\" data-align=\"left\">mon dd yyyy hh:miAM<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>1<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>101<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">United States<\/td><td class=\"has-text-align-left\" data-align=\"left\">1 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">mm\/dd\/yy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">101 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">mm\/dd\/yyyy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>2<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>102<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">ANSI<\/td><td class=\"has-text-align-left\" data-align=\"left\">2 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">yy.mm.dd<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">102 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">yyyy.mm.dd<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>3<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>103<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">England\/Frankreich<\/td><td class=\"has-text-align-left\" data-align=\"left\">3 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">dd\/mm\/yy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">103 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">dd\/mm\/yyyy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>4<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>104<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">Deutschland<\/td><td class=\"has-text-align-left\" data-align=\"left\">4 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">dd.mm.yy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">104 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">dd.mm.yyyy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>10<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>110<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">United States<\/td><td class=\"has-text-align-left\" data-align=\"left\">10 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">mm-dd-yy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">110 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">mm-dd-yyyy<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>12<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>112<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">ISO<\/td><td class=\"has-text-align-left\" data-align=\"left\">12 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">yymmdd<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">112 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">yyyymmdd<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>13<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\"><strong>113<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\" rowspan=\"2\">Europe (Default)<br>including time with milliseconds<\/td><td class=\"has-text-align-left\" data-align=\"left\">13 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">dd mon yyyy hh:mi:ss:mmm<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\">113 =<\/td><td class=\"has-text-align-left\" data-align=\"left\">(24 Stunden)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorFormatStrings\">Format Identifier<\/h1>\n\n\n\n<p>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:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>Format Identifyer<\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\"><strong>Description<\/strong><\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>d<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The day of the month, from 1 to 31.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>dd<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The day of the month, from 01 to 31.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>ddd<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The abbreviated name of the day of the week.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>dddd<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The full name of the day of the week.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>f<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The tenth of a second in a date and time value.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>ff<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The hundredth of a second in a date and time value.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>fff<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The milliseconds in a date and time value.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>h<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The hour, from 1 to 12 (12-hour format).<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>hh<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The hour, from 01 to 12 (12-hour format).<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>H<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The hour, from 0 to 23 (24-hour format).<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>HH<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The hour, from 00 to 23 (24-hour format).<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>m<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The minute, from 0 to 59.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>mm<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The minute, from 00 to 59.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>M<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The month, from 1 to 12.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>MM<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The month, from 01 to 12.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>s<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The second, from 0 to 59.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>ss<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The second, from 00 to 59.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>yy<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The year, from 00 to 99.<\/td><\/tr><tr><td class=\"has-text-align-left\" data-align=\"left\"><strong>yyyy<\/strong><strong><\/strong><\/td><td class=\"has-text-align-left\" data-align=\"left\">The year as a four-digit number.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorUserDefinedFunctions\">User-defined Functions<\/h1>\n\n\n\n<p>The implementations of the conversion functions expect a date in the <strong>p_Date <\/strong>parameter, which is transferred as text. The <strong>p_DateStyle <\/strong>parameter specifies a user-defined format string or one of the <em>style<\/em> parameters mentioned above. The supported format strings and <em>style <\/em>codes can be found in the code. The expected format strings do not correspond exactly to the format identifiers as specified in the section <a href=\"#AnchorFormatStrings\">Format Identifier<\/a>. 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 <em>style <\/em>parameter or the formatting string is specifically queried and implemented. The format string is queried using <em>Case Insensitive<\/em>.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<p class=\"has-cyan-bluish-gray-background-color has-background\">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 <strong>[\u2026]<\/strong>. When using these functions, this character string must be replaced by the corresponding translations.<\/p>\n<\/div><\/div>\n\n\n\n<h2 class=\"wp-block-heading\">Declaration<\/h2>\n\n\n\n<p>The declaration of the user-defined stored functions can generally be specified as follows:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Description<\/h3>\n\n\n\n<p>Converts a transferred input value into the target data type date, time, datetime or datetime2. If the input value cannot be converted, <em>NULL<\/em> is returned. The transferred value is treated as <em>Case Insensitive<\/em>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Syntax<\/h3>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code>&nbsp;1: &#91;dbo].&#91;fnConvertDate] (@p_Date AS nvarchar(50), @p_DateStyle nvarchar(50))<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Arguments<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>p_Date<br><\/strong>Specifies the input value to be converted.<\/li>\n\n\n\n<li><strong>p_DateStyle<br><\/strong>Specifies a format string according to which a date is passed in <strong>p_Date<\/strong>, or a <em>style <\/em>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.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Return<\/h3>\n\n\n\n<p>Returns the converted value as a value of type <em>date<\/em>, <em>time<\/em>, <em>datetime <\/em>or <em>datetime2 <\/em>if the conversion is successful. If the input value cannot be converted, <em>NULL <\/em>is returned. If <em>NULL <\/em>or an empty string is passed, the functions returns <em>NULL<\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"AnchorfnConvertDate\">[dbo].[fnConvertDate]<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: CREATE FUNCTION &#91;dbo].&#91;fnConvertDate] (@p_Date AS nvarchar(50), @p_DateStyle nvarchar(50))\n 2: RETURNS date\n 3: AS\n 4: BEGIN\n 5:    -- --------------------------------------------------------------------------------\n 6:    -- Declare variables\n 7:    -- --------------------------------------------------------------------------------\n 8:    DECLARE @returnValue       AS date;\n 9: \n10:    SET @p_DateStyle = LOWER(@p_DateStyle);\n11: \n12:    -- --------------------------------------------------------------------------------\n13:    -- Workload\n14:    -- --------------------------------------------------------------------------------\n15:    IF &#91;dbo].&#91;fnIsNullOrEmpty](@p_Date, 1) = 1\n16:       BEGIN\n17:          SET @returnValue = NULL;\n18:       END\n19:    ELSE IF @p_DateStyle IN (   \n20:                                N'yyyy-mm-dd'                   , N'23'\n21:                               ,N'mon dd yyyy hh:miam'          , N'100'\n22:                               ,N'mm\/dd\/yyyy'                   , N'101'\n23:                               ,N'yyyy.mm.dd'                   , N'102'\n24:                               ,&#91;...]\n25:                            )                                            \n26:       BEGIN\n27:          SET @returnValue = CASE\n28:                                WHEN @p_DateStyle IN (N'yyyy-mm-dd'         , N'23' ) \n29:                                   THEN TRY_CONVERT(date, @p_Date,  23)\n30:                                WHEN @p_DateStyle IN (N'mon dd yyyy hh:miam', N'100') \n31:                                   THEN TRY_CONVERT(date, @p_Date, 100)\n32:                                WHEN @p_DateStyle IN (N'mm\/dd\/yyyy'         , N'101') \n33:                                   THEN TRY_CONVERT(date, @p_Date, 101)\n34:                                WHEN @p_DateStyle IN (N'yyyy.mm.dd'         , N'102') \n35:                                   THEN TRY_CONVERT(date, @p_Date, 102)\n36:                                WHEN @p_DateStyle IN (N'dd\/mm\/yyyy'         , N'103') \n37:                                   THEN TRY_CONVERT(date, @p_Date, 103)\n38:                                &#91;...]\n39:                             END;\n40:       END\n41:    ELSE \n42:       BEGIN\n43:          SET @returnValue = NULL;\n44:       END;\n45: \n46:    RETURN @returnValue;\n47: END;\n <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"AnchorfnConvertTime\">[dbo].[fnConvertTime]<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: CREATE FUNCTION &#91;dbo].&#91;fnConvertTime](@p_Time AS nvarchar(50), @p_TimeStyle nvarchar(50))\n 2: RETURNS time(7)\n 3: AS\n 4: BEGIN\n 5:    -- --------------------------------------------------------------------------------\n 6:    -- Declare variables\n 7:    -- --------------------------------------------------------------------------------\n 8:    DECLARE @returnValue       AS time;\n 9: \n10:    -- --------------------------------------------------------------------------------\n11:    -- Initialize variables\n12:    -- --------------------------------------------------------------------------------\n13:    SET @p_TimeStyle = LOWER(@p_TimeStyle)\n14: \n15:    -- --------------------------------------------------------------------------------\n16:    -- Workload\n17:    -- --------------------------------------------------------------------------------\n18:    IF &#91;dbo].&#91;fnIsNullOrEmpty](@p_Time, 1) = 1\n19:       BEGIN\n20:          SET @returnValue = NULL;\n21:       END\n22:    ELSE IF @p_TimeStyle IN ( N'yyyymmddhhmmss'\n23:                             ,N'yyyymmddhhmmssf'\n24:                             ,N'yyyymmddhhmmssff'\n25:                             ,&#91;...]\n26:                             ,N'yyyymmddhhmmssfffffff')\n27:       BEGIN \n28:          SET @returnValue = TRY_CONVERT( time(7)                        -- 202304181011121234567\n29:                                         ,SUBSTRING(@p_Time, 1, 4)+'-'+  -- 2023\n30:                                          SUBSTRING(@p_Time, 5, 2)+'-'+  -- 04\n31:                                          SUBSTRING(@p_Time, 7, 2)+' '+  -- 18\n32:                                          SUBSTRING(@p_Time, 9, 2)+':'+  -- 10\n33:                                          SUBSTRING(@p_Time,11, 2)+':'+  -- 11\n34:                                          SUBSTRING(@p_Time,13, 2)+'.'+  -- 12\n35:                                          SUBSTRING(@p_Time,15, 7)       -- 1234567\n36:                                         ,120\n37:                                        ) \n38:       END\n39:    ELSE IF @p_TimeStyle IN ( N'hhmmss'\n40:                             ,N'hhmmssf'\n41:                             ,N'hhmmssff'\n42:                             ,N'hhmmssfff'\n43:                             ,&#91;...]\n44:                             ,N'hhmmssfffffff')\n45:       BEGIN \n46:          SET @returnValue = TRY_CONVERT( time(7)                        -- 1011121234567\n47:                                         ,SUBSTRING(@p_Time, 1, 2)+':'+  -- 10\n48:                                          SUBSTRING(@p_Time, 3, 2)+':'+  -- 11\n49:                                          SUBSTRING(@p_Time, 5, 2)+'.'+  -- 12\n50:                                          SUBSTRING(@p_Time, 7, 7)       -- 1234567\n51:                                         ,120\n52:                                        ) \n53:       END\n54:    ELSE IF @p_TimeStyle IN (   \n55:                                N'mon dd yyyy hh:miam'          , N'100' \n56:                               ,N'mm\/dd\/yyyy'                   , N'101' \n57:                               ,N'yyyy.mm.dd'                   , N'102' \n58:                               ,N'dd\/mm\/yyyy'                   , N'103' \n59:                               ,&#91;...]\n60:                            )\n61:       BEGIN\n62:          SET @returnValue = CASE\n63:                                WHEN @p_TimeStyle IN (N'yyyy.mm.dd'                , N'102') \n64:                                   THEN TRY_CONVERT(time(7), @p_Time, 102) \n65:                                WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss'       , N'120')\n66:                                   THEN TRY_CONVERT(time(7), @p_Time, 120) \n67:                                WHEN @p_TimeStyle IN (N'yyyy-mm-dd hh:mi:ss.mmm'   , N'121')\n68:                                   THEN TRY_CONVERT(time(7), @p_Time, 121) \n69:                                WHEN @p_TimeStyle IN (N'yyyy-mm-ddthh:mi:ss.mmm'   , N'126')\n70:                                   THEN TRY_CONVERT(time(7), @p_Time, 126) \n71:                                &#91;...]\n72:                             END;\n73:       END\n74:    ELSE \n75:       BEGIN\n76:          SET @returnValue = NULL;\n77:       END;\n78: \n79:    RETURN @returnValue;\n80: END;\n <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"AnchorfnConvertDateTime\">[dbo].[fnConvertDateTime]<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: CREATE FUNCTION &#91;dbo].&#91;fnConvertDateTime] \n    (\n        @p_date      AS nvarchar(50)\n       ,@p_dateStyle AS nvarchar(50)\n    )\n 2: RETURNS datetime\n 3: AS\n 4: BEGIN\n 5:    -- --------------------------------------------------------------------------------\n 6:    -- Declare variables\n 7:    -- --------------------------------------------------------------------------------\n 8:    DECLARE @returnValue       AS datetime;\n 9: \n10:    -- --------------------------------------------------------------------------------\n11:    -- Initialize variables\n12:    -- --------------------------------------------------------------------------------\n13:    SET @p_dateStyle = LOWER(@p_dateStyle);\n14: \n15:    -- --------------------------------------------------------------------------------\n16:    -- Workload\n17:    -- --------------------------------------------------------------------------------\n18:    IF &#91;dbo].&#91;fnIsNullOrEmpty](@p_Date, 1) = 1\n19:       BEGIN\n20:          SET @returnValue = NULL;\n21:       END\n22:    ELSE IF @p_dateStyle IN (  N'yyyymmddhhmmss'\n                                , N'yyyymmddhhmmssf'\n                                , N'yyyymmddhhmmssff'\n                                , N'yyyymmddhhmmssfff'\n                               )\n23:       BEGIN \n24:          SET @returnValue = TRY_CONVERT(datetime2                      -- 20230418101112123\n25:                                        ,SUBSTRING(@p_date, 1, 4)+'-'+  -- 2023\n26:                                         SUBSTRING(@p_date, 5, 2)+'-'+  -- 04\n27:                                         SUBSTRING(@p_date, 7, 2)+' '+  -- 18\n28:                                         SUBSTRING(@p_date, 9, 2)+':'+  -- 10\n29:                                         SUBSTRING(@p_date,11, 2)+':'+  -- 11\n30:                                         SUBSTRING(@p_date,13, 2)+'.'+  -- 12\n31:                                         SUBSTRING(@p_date,15, 3)       -- 123\n32:                                        ,120\n33:                                        ) \n34:       END\n35:    ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'\n36:       BEGIN \n37:          SET @returnValue = TRY_CONVERT(datetime \n38:                                        ,SUBSTRING(@p_date, 1, 4)+'-'+\n39:                                         SUBSTRING(@p_date, 5, 2)+'-'+\n40:                                         SUBSTRING(@p_date, 7, 2)+' '+\n41:                                         SUBSTRING(@p_date,10, 2)+':'+\n42:                                         SUBSTRING(@p_date,12, 2)+':'+\n43:                                         SUBSTRING(@p_date,14, 2)+'.'+\n44:                                         SUBSTRING(@p_date,16, 3)\n45:                                        ,121\n46:                                        ) \n47:       END\n48:    ELSE IF @p_dateStyle IN (   \n49:                                N'yyyy-mm-dd'         , N'23'\n50:                               ,N'mon dd yyyy hh:miam', N'100'\n51:                               ,N'mm\/dd\/yyyy'         , N'101'\n52:                               ,N'yyyy.mm.dd'         , N'102'\n53:                               ,&#91;...]\n54:                          )\n55:       BEGIN\n56:          SET @returnValue = CASE\n57:                                WHEN @p_dateStyle IN (N'yyyy-mm-dd'         , N'23' ) \n58:                                   THEN TRY_CONVERT(datetime, @p_date,  23)\n59:                                WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100') \n60:                                   THEN TRY_CONVERT(datetime, @p_date, 100)\n61:                                WHEN @p_dateStyle IN (N'mm\/dd\/yyyy'         , N'101') \n62:                                   THEN TRY_CONVERT(datetime, @p_date, 101)\n63:                                WHEN @p_dateStyle IN (N'yyyy.mm.dd'         , N'102') \n64:                                   THEN TRY_CONVERT(datetime, @p_date, 102)\n65:                                &#91;...]\n66:                             END;\n67:       END\n68:    ELSE \n69:       BEGIN\n70:          SET @returnValue = NULL;\n71:       END;\n72: \n73:    RETURN @returnValue;\n74: END;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"AnchorfnConvertDateTime2\">[dbo].[fnConvertDateTime2]<\/h2>\n\n\n\n<pre class=\"wp-block-code has-background\" style=\"background-color:#eeeeee\"><code> 1: CREATE FUNCTION &#91;dbo].&#91;fnConvertDateTime2] (@p_date AS nvarchar(50), @p_dateStyle nvarchar(50))\n 2: RETURNS datetime2(7)\n 3: AS\n 4: BEGIN\n 5:    -- --------------------------------------------------------------------------------\n 6:    -- Declare variables\n 7:    -- --------------------------------------------------------------------------------\n 8:    DECLARE @returnValue       AS datetime2(7);\n 9: \n10:    -- --------------------------------------------------------------------------------\n11:    -- Initialize variables\n12:    -- --------------------------------------------------------------------------------\n13:    SET @p_dateStyle = LOWER(@p_dateStyle)\n14: \n15:    -- --------------------------------------------------------------------------------\n16:    -- Workload\n17:    -- --------------------------------------------------------------------------------\n18:    IF &#91;dbo].&#91;fnIsNullOrEmpty](@p_Date, 1) = 1\n19:       BEGIN\n20:          SET @returnValue = NULL;\n21:       END\n22:    ELSE IF @p_dateStyle IN ( N'yyyymmddhhmmss'\n23:                             ,N'yyyymmddhhmmssf'\n24:                             ,N'yyyymmddhhmmssff'\n25:                             ,&#91;...]\n26:                             ,N'yyyymmddhhmmssfffffff')\n27:       BEGIN \n28:          SET @returnValue = TRY_CONVERT( datetime2  -- 202304181011121234567\n29:                                         ,SUBSTRING(@p_date, 1, 4)+'-'+  -- 2023\n30:                                          SUBSTRING(@p_date, 5, 2)+'-'+  -- 04\n31:                                          SUBSTRING(@p_date, 7, 2)+' '+  -- 18\n32:                                          SUBSTRING(@p_date, 9, 2)+':'+  -- 10\n33:                                          SUBSTRING(@p_date,11, 2)+':'+  -- 11\n34:                                          SUBSTRING(@p_date,13, 2)+'.'+  -- 12\n35:                                          SUBSTRING(@p_date,15, 7)       -- 1234567\n36:                                         ,120\n37:                                        ) \n38:       END\n39:    ELSE IF @p_dateStyle = N'yyyymmdd_hhmissmmm'\n40:       BEGIN \n41:          SET @returnValue = TRY_CONVERT( datetime2(7) \n42:                                         ,SUBSTRING(@p_date, 1, 4)+'-'+\n43:                                          SUBSTRING(@p_date, 5, 2)+'-'+\n44:                                          SUBSTRING(@p_date, 7, 2)+' '+\n45:                                          SUBSTRING(@p_date,10, 2)+':'+\n46:                                          SUBSTRING(@p_date,12, 2)+':'+\n47:                                          SUBSTRING(@p_date,14, 2)+'.'+\n48:                                          SUBSTRING(@p_date,16, 3)\n49:                                         ,121\n50:                                        ) \n51:       END\n52:    ELSE IF @p_dateStyle IN (   \n53:                              N'mon dd yyyy hh:miam'          , N'100'\n54:                             ,N'mm\/dd\/yyyy'                   , N'101'\n55:                             ,N'yyyy.mm.dd'                   , N'102'\n56:                             ,N'dd\/mm\/yyyy'                   , N'103'\n57:                             ,&#91;...]\n58:                            )\n59:       BEGIN\n60:          SET @returnValue = CASE\n61:                                WHEN @p_dateStyle IN (N'yyyy.mm.dd'         , N'102') \n62:                                   THEN TRY_CONVERT(datetime2(7), @p_date, 102) \n63:                                WHEN @p_dateStyle IN (N'yyyy\/mm\/dd'         , N'111') \n64:                                   THEN TRY_CONVERT(datetime2(7), @p_date, 111) \n65:                                WHEN @p_dateStyle IN (N'yyyymmdd'           , N'112') \n66:                                   THEN TRY_CONVERT(datetime2(7), @p_date, 112) \n67:                                WHEN @p_dateStyle IN (N'mon dd yyyy hh:miam', N'100') \n68:                                   THEN TRY_CONVERT(datetime2(7), @p_date, 100) \n69:                             END;\n70:       END\n71:    ELSE \n72:       BEGIN\n73:          SET @returnValue = NULL;\n74:       END;\n75: \n76:    RETURN @returnValue;\n77: END;\n<\/code><\/pre>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorSummary\">Summary<\/h1>\n\n\n\n<p>When converting a text into a date, <em>SQL Server <\/em>only supports the date formats documented under <em><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/t-sql\/functions\/cast-and-convert-transact-sql?view=sql-server-ver16#date-and-time-styles\">CAST&nbsp;and&nbsp;CONVERT&nbsp;(Transact-SQL)<\/a><\/em> and assigns a style code to the formats listed there.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p> <\/p>\n\n\n\n<h1 class=\"wp-block-heading\" id=\"AnchorRelatedPosts\">Related Posts<\/h1>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1594\">Data quality in an ETL process<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1181\">Datenqualit\u00e4t \/\/ Grundlagen der Typ-Konvertierung mit T-SQL<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1576\">TRY_CONVERT \/\/ Converting data to&nbsp;bigint, int, smallint, tinyint<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1564\">TRY_CONVERT \/\/ Converting data to decimal or numeric<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1607\">TRY_CONVERT \/\/ Converting data to money, smallmoney<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1651\">TRY_CONVERT \/\/ Converting data to float, real<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1673\">TRY_CONVERT \/\/ Converting data to bit<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1733\">TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>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: To convert a date supplied as text, the CONVERT and &#8230; <a title=\"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time\" class=\"read-more\" href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1733&#038;lang=en\" aria-label=\"Mehr Informationen \u00fcber TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time\">Weiterlesen<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[106,108],"tags":[110,112,114,116],"class_list":["post-1733","post","type-post","status-publish","format-standard","hentry","category-all-languages-en","category-english-en","tag-data-quality-en","tag-data-type-en","tag-design-pattern-en","tag-try_convert-en"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time - Just another SQL blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en\" \/>\n<meta property=\"og:locale\" content=\"de_DE\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time - Just another SQL blog\" \/>\n<meta property=\"og:description\" content=\"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: To convert a date supplied as text, the CONVERT and ... Weiterlesen\" \/>\n<meta property=\"og:url\" content=\"https:\/\/staging.sql.marcus-belz.de\/?p=1733&amp;lang=en\" \/>\n<meta property=\"og:site_name\" content=\"Just another SQL blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-09T12:00:16+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-05-08T12:08:37+00:00\" \/>\n<meta name=\"author\" content=\"marcus\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschrieben von\" \/>\n\t<meta name=\"twitter:data1\" content=\"marcus\" \/>\n\t<meta name=\"twitter:label2\" content=\"Gesch\u00e4tzte Lesezeit\" \/>\n\t<meta name=\"twitter:data2\" content=\"8\u00a0Minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en\"},\"author\":{\"name\":\"marcus\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\"},\"headline\":\"TRY_CONVERT \\\/\\\/ Converting data to date, datetime, datetime2, time\",\"datePublished\":\"2024-03-09T12:00:16+00:00\",\"dateModified\":\"2026-05-08T12:08:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en\"},\"wordCount\":1249,\"keywords\":[\"Data Quality\",\"Data type\",\"Design Pattern\",\"TRY_CONVERT\"],\"articleSection\":[\"All Languages\",\"English\"],\"inLanguage\":\"de\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en\",\"name\":\"TRY_CONVERT \\\/\\\/ Converting data to date, datetime, datetime2, time - Just another SQL blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#website\"},\"datePublished\":\"2024-03-09T12:00:16+00:00\",\"dateModified\":\"2026-05-08T12:08:37+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en#breadcrumb\"},\"inLanguage\":\"de\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?p=1733&lang=en#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"TRY_CONVERT \\\/\\\/ Converting data to date, datetime, datetime2, time\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#website\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/\",\"name\":\"Just another SQL blog\",\"description\":\"Marcus Belz \u00b7 SQL Server \u00b7 ETL \u00b7 Datenqualit\u00e4t\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"de\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/#\\\/schema\\\/person\\\/98ab042e47d7286f64530ee18f20f675\",\"name\":\"marcus\",\"url\":\"https:\\\/\\\/staging.sql.marcus-belz.de\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time - Just another SQL blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en","og_locale":"de_DE","og_type":"article","og_title":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time - Just another SQL blog","og_description":"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: To convert a date supplied as text, the CONVERT and ... Weiterlesen","og_url":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en","og_site_name":"Just another SQL blog","article_published_time":"2024-03-09T12:00:16+00:00","article_modified_time":"2026-05-08T12:08:37+00:00","author":"marcus","twitter_card":"summary_large_image","twitter_misc":{"Geschrieben von":"marcus","Gesch\u00e4tzte Lesezeit":"8\u00a0Minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en#article","isPartOf":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en"},"author":{"name":"marcus","@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675"},"headline":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time","datePublished":"2024-03-09T12:00:16+00:00","dateModified":"2026-05-08T12:08:37+00:00","mainEntityOfPage":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en"},"wordCount":1249,"keywords":["Data Quality","Data type","Design Pattern","TRY_CONVERT"],"articleSection":["All Languages","English"],"inLanguage":"de"},{"@type":"WebPage","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en","url":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en","name":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time - Just another SQL blog","isPartOf":{"@id":"https:\/\/staging.sql.marcus-belz.de\/#website"},"datePublished":"2024-03-09T12:00:16+00:00","dateModified":"2026-05-08T12:08:37+00:00","author":{"@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675"},"breadcrumb":{"@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en#breadcrumb"},"inLanguage":"de","potentialAction":[{"@type":"ReadAction","target":["https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/staging.sql.marcus-belz.de\/?p=1733&lang=en#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/staging.sql.marcus-belz.de\/"},{"@type":"ListItem","position":2,"name":"TRY_CONVERT \/\/ Converting data to date, datetime, datetime2, time"}]},{"@type":"WebSite","@id":"https:\/\/staging.sql.marcus-belz.de\/#website","url":"https:\/\/staging.sql.marcus-belz.de\/","name":"Just another SQL blog","description":"Marcus Belz \u00b7 SQL Server \u00b7 ETL \u00b7 Datenqualit\u00e4t","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/staging.sql.marcus-belz.de\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"de"},{"@type":"Person","@id":"https:\/\/staging.sql.marcus-belz.de\/#\/schema\/person\/98ab042e47d7286f64530ee18f20f675","name":"marcus","url":"https:\/\/staging.sql.marcus-belz.de\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1733"}],"version-history":[{"count":11,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1733\/revisions"}],"predecessor-version":[{"id":1831,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=\/wp\/v2\/posts\/1733\/revisions\/1831"}],"wp:attachment":[{"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/staging.sql.marcus-belz.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}