Hadi Fadlallah

SQL Server functions for converting a String to a Date

February 6, 2020 by

While working with raw data, you may frequently face date values stored as text. Converting these values to a date data type is very important since dates may be more valuable during analysis. In SQL Server, converting a string to date can be achieved in different approaches.

In general, there are two types of data type conversions:

  1. Implicit where conversions are not visible to the user; data type is changed while loading data without using any function
  2. Explicit where conversions are visible to the user and they are performed using CAST or CONVERT functions or other tools

In this article, we will explain how a string to date conversion can be achieved implicitly, or explicitly in SQL Server using built-in functions such as CAST(), TRY_CAST(), CONVERT(), TRY_CONVERT() and TRY_PARSE().

  • Note: Before we start, please note that some of the SQL statements used are meaningless from the data context perspective and are just used to explain the concept.

SQL Server: convert string to date implicitly

As mentioned above, converting a data type implicitly is not visible to the user, as an example when you are comparing two fields or values having different data types:

In SQL Server, converting string to date implicitly depends on the string date format and the default language settings (regional settings); If the date stored within a string is in ISO formats: yyyyMMdd or yyyy-MM-ddTHH:mm:ss(.mmm), it can be converted regardless of the regional settings, else the date must have a supported format or it will throw an exception, as an example while working under the regional settings “EN-US”, if we try to convert a string with dd/MM/yyyy format it will fail since it tries to convert it as MM/dd/yyyy format which is supported.

Will throw the following exception:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Screenshot:

Using SQL Server, convert string to date implicitly failure if date format is not supported

But, if we switch the day and month parts, it will succeed:

Screenshot:

Using SQL Server, convert string to date implicitly

You can check out this official documentation here to learn more about how to change SQL Server language settings.

Additionally, you can read more about implicitly converting date types in SQL Server, by referring to this article: Implicit conversion in SQL Server.

SQL Server: Convert string to date explicitly

The second approach for converting data types is the explicit conversion which is done by using some functions or tools. In SQL Server, converting a string to date explicitly can be achieved using CONVERT(). CAST() and PARSE() functions.

CAST()

CAST() is the most basic conversion function provided by SQL Server. This function tries to convert given value to a specified data type (data type length can only be specified).

Example:

Result:

Using SQL Server, convert string to date explicitly

Note that in SQL Server, converting a string to date using CAST() function depends on the language settings similar to implicit conversion, as we mentioned in the previous section, so you can only convert ISO formats or supported formats by the current language settings.

CONVERT()

CONVERT() function is more advanced than CAST() since the conversion style can be specified. This function takes 3 arguments: (1) the desired data type, (2) the input value and (3) the style number (optional).

If the style number is not passed to the function, it acts like the CAST() function. But, if the style argument is passed, it will try to convert the value based on that style. As an example, if we try to convert “13/12/2019” value to date without specifying the style number, it will fail since it is not supported by the current language setting:

Result:

CONVERT function will fail if date format is not supported and style number is not provided

But, if we pass 103 as style number (103 is corresponding of dd/MM/yyyy date format), it will succeed:

Result:

Providing the relevant style number to CONVERT() will solve unsupported format issue.

For more information about CONVERT() function and date style numbers, you can refer to the following articles:

PARSE()

PARSE() is SQL CLR function that use .Net framework Parse() function. PARSE() syntax is as follows:

PARSE(<value> AS <data type> [USING <culture>])

If the culture info is not specified, PARSE() acts similar to CAST() function, but when the culture is passed within the expression, the function tries to convert the value to the desired data type using this culture. As an example, if we try to parse 13/12/2019 value without passing the culture information, it will fail since “dd/MM/yyyy” is not supported by the default language settings.

Without specifying a relevant culture in the input expression, PARSE() function is unable to convert unsupported data formats

But, if we pass “AR-LB” as culture (Arabic – Lebanon), where “dd/MM/yyyy” is supported, the conversion succeeds:

Passing relevant culture will let PARSE() function convert unsupported date formats by current language.

TRY_CAST(), TRY_CONVERT() and TRY_PARSE()

One of the main issues of the data type conversion functions is that they cannot handle the erroneous value. As an example, many times you may face bad date values such as “01/01/0000”; these values cannot be converted and will throw a data conversion exception.

To solve this issue, you can use TRY_CAST(), TRY_CONVERT() or TRY_PARSE() functions to check if the value can be converted or not, if so, the function will return the conversion result, else it will return a NULL value.

Example:

Result:

In SQL Server, convert string to date exception can be handled using TRY_CAST(), TRY_CONVERT(0 or TRY_PARSE() functions.

CAST() vs CONVERT() vs PARSE()

To understand the differences among these conversion functions and also to decide which function to use in which scenario, you can refer to this site.

Conclusion

In this article, we explained data conversion approaches in general. Then we showed how, while using SQL Server, converting a string to date can be achieved using these approaches. We explained the system functions provided by SQL Server by giving some examples and external links that provide more details.

Hadi Fadlallah
Data types, String functions

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views