How to convert a string to a date format in SQL Server ? T-SQL

Different solutions to convert a date stored in a text to a date format using T-SQL functions.

When working with data in SQL Server, you may encounter situations where you must convert dates that are stored as strings to native date format of SQL. To perform meaningful date-based operations, it’s crucial to convert these strings into proper date data types. In this blog post, we’ll explore various methods to convert a string to a date in SQL Server, complete with code examples.

1. Use implicit or explicit methods to convert a string to date format

Convert SQL Server string implicitly

Implicit string-to-date conversion occurs automatically without the need for specific functions. SQL Server relies on the string’s format and the default language settings for this type of conversion. If the string follows ISO formats like yyyyMMdd or yyyy-MM-ddTHH:mm:ss(.mmm), it can be converted regardless of regional settings.

However, unsupported formats will result in an exception. So the implicit conversion is not recomended. Let’s consider a basic example:

SELECT *
FROM   dbo.Sales
WHERE  DateAsString = '20230115';

In this case, the ‘20230115’ string will be implicitly converted to a date if the format matches the system’s settings.

Convert SQL Server string explicitly

Explicit string-to-date conversion provides more control over the process and is achieved using functions like:

CAST()
CONVERT()
and PARSE().

Moreover, these functions are particularly useful when dealing with non-standard date formats.

2. Convert a date using the CAST function

The CAST() function allows you to convert a string to a date by specifying the data type.

DECLARE @DateString VARCHAR(10) = '2023-01-15';
DECLARE @DateValue  DATE;

SET @DateValue = CAST(@DateString AS DATE);

In this example, we declare a string variable @DateString and use CAST() to convert it to a DATE data type.

3. Use the CONVERT SQL Server function to transform a date

The CONVERT() function offers more flexibility as you can specify both the desired data type and the style for the conversion.

DECLARE @DateString VARCHAR(10) = '01/15/2023';
DECLARE @DateValue DATE;

-- Explicitly use style 101 for mm/dd/yyyy format
SET @DateValue = CONVERT(DATE, @DateString, 101);

In this example, we use CONVERT() with style 101 to convert the string ’01/15/2023′ to a DATE data type.

*Use T-SQL functions to convert a string to a SQL Server date format*

4. Use the PARSE conversion option

The PARSE() function, when combined with a culture parameter, allows you to convert strings with specific date formats.

DECLARE @DateString VARCHAR(10) = '15/01/2023';
DECLARE @DateValue DATE;

-- Using the 'en-US' culture for dd/MM/yyyy format
SET @DateValue = PARSE(@DateString AS DATE USING 'en-US');

Here, we specify the culture as ‘en-US’ to ensure correct conversion of the ’15/01/2023′ string.

5. Using the FORMAT function

You can use the FORMAT() function to convert a string to a date by specifying the format explicitly. This method provides control over the input format. Please find an example below:

DECLARE @DateString VARCHAR(10) = '01/15/2023';
DECLARE @DateValue DATE;

SET @DateValue = FORMAT(CONVERT(DATETIME, @DateString, 101), 'MM/dd/yyyy');

-- @DateValue will contain the date '2023-01-15'

Moreover the FORMAT and CONVERT combination is a widely used technique to convert text to date.

In this example, we start with a string called @DateString holding the date ’01/15/2023′. To turn it into a date that computers can understand, we use the CONVERT function, which changes the string into a DATETIME value, taking note of the format ‘mm/dd/yyyy’.

Next, we use the FORMAT() function to make sure it looks like ‘MM/dd/yyyy’. Finally, the result is saved in the @DateValue variable, and it now holds the date ‘2023-01-15’ in the desired format.

Handling Conversion Errors with the TRY_* functions

Handling conversion errors is important to avoid unexpected issues. SQL Server provides functions like TRY_CAST(), TRY_CONVERT(), and TRY_PARSE() for this purpose. These functions return NULL if the conversion fails, allowing you to gracefully manage erroneous data.

DECLARE @DateString VARCHAR(10) = '15/01/2023';
DECLARE @DateValue DATE;

-- Using the 'en-US' culture for dd/MM/yyyy format
SET @DateValue = PARSE(@DateString AS DATE USING 'en-US');

In this example, the TRY_CAST() built-in SQL function is used to convert an invalid string to a date, resulting in a NULL value for @DateValue.

SQL Server offers multiple conversion options

To convert a string to a date format in SQL Server is a common but often mandatory task when dealing with date-related data. Indeed, you often need to perform implicit or explicit conversions for example, or handle conversion errors gracefully.

To do so, SQL Server offers a range of functions to ensure accurate and reliable date conversion. By mastering these techniques, you’ll be better equipped to work with date values in your SQL Server databases. These functions can be used for columns as well as for variables of course.