4 Ways to Convert MM/DD/YYYY to DATE in SQL Server

by

Converting a string in ‘MM/DD/YYYY’ format to a DATE data type in SQL Server is a common task. Below are four options for getting the job done.

1. Using the CONVERT() Function with Style Code

The CONVERT() function is a classic and highly versatile way to handle data type conversions. It allows you to specify a style code to tell SQL Server how to interpret the input string.

The style code for ‘MM/DD/YYYY’ is 101.

Here’s an example:

DECLARE @mmddyyyy_string VARCHAR(10) = '08/03/2025';
SELECT CONVERT(DATE, @mmddyyyy_string, 101) AS converted_date;

Output:

converted_date  
----------------
      2025-08-03

Explanation of arguments:

  • DATE: The target data type.
  • @mmddyyyy_string: The string you want to convert.
  • 101: The style code for mm/dd/yyyy.

This is a reliable and performant method when you know the input format.

2. Using the TRY_CONVERT() Function

The TRY_CONVERT() function is similar to CONVERT(), but it’s “safer.” If the conversion fails (e.g., the input string is invalid), it returns NULL instead of raising an error. This can be useful for data cleansing and ETL processes where you can’t guarantee the cleanliness of your source data.

Here’s an example of a valid conversion:

DECLARE @mmddyyyy_string_valid VARCHAR(10) = '08/03/2025';
SELECT TRY_CONVERT(DATE, @mmddyyyy_string_valid, 101) AS converted_date;

Result:

converted_date  
----------------
      2025-08-03

Here’s an invalid conversion:

DECLARE @mmddyyyy_string_invalid VARCHAR(10) = '13/03/2025'; -- Month '13' is invalid
SELECT TRY_CONVERT(DATE, @mmddyyyy_string_invalid, 101) AS converted_date;

Output:

converted_date  
----------------
            NULL

As expected, we get NULL due to the invalid date string being passed in. I passed 13 for the month but there are only 12 months in the year.

If we’d used CONVERT(), we’d have gotten an error:

DECLARE @mmddyyyy_string_invalid VARCHAR(10) = '13/03/2025'; -- Month '13' is invalid
SELECT CONVERT(DATE, @mmddyyyy_string_invalid, 101) AS converted_date;

Output:

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

3. Using the CAST() Function (Implicit Conversion)

CAST() is a simpler way to convert data types, but it doesn’t offer style codes, and so you’ll need to be very careful when using this method. When converting a string to a DATE, SQL Server will try to interpret the string based on the default language and date format settings of the server/current session:

DECLARE @mmddyyyy_string VARCHAR(10) = '08/03/2025';
SELECT CAST(@mmddyyyy_string AS DATE) AS converted_date;

Sample result:

converted_date  
----------------
      2025-08-03

In this example, we got the same result as the previous examples. But this isn’t always necessarily going to be the case. For ‘MM/DD/YYYY’ to be successfully converted using CAST, the session’s default date format must be configured to US English (or a similar format where month comes first).

Therefore, this method is less reliable because the outcome depends on the server’s and, more specifically, the current session’s language and date format settings. If your session is configured for a different language (e.g., British English, where the default is dd/mm/yyyy), this conversion will fail or produce incorrect results. For example, '08/03/2025' might be interpreted as August 3, 2025 in a US English environment, but as March 8, 2025 in a British English environment.

Here’s an example that demonstrates how these settings can impact the result:

-- Change the session language to British English
SET LANGUAGE 'British';
GO

-- Verify the change
SELECT @@LANGUAGE AS CurrentLanguage;

-- Now, CAST will interpret dates in a DD/MM/YYYY format
SELECT CAST('08/03/2025' AS DATE) AS CastedDate_British;
GO

-- Change the language back to US English
SET LANGUAGE 'us_english';
GO

-- Verify the change
SELECT @@LANGUAGE AS CurrentLanguage;

-- Now, CAST will interpret dates in a MM/DD/YYYY format
SELECT CAST('08/03/2025' AS DATE) AS CastedDate_US;

Sample result:

Changed language setting to British.
CurrentLanguage                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------
British 
                                                                                                                        
CastedDate_British
------------------
        2025-03-08

Changed language setting to us_english.
CurrentLanguage                                                                                                                 
--------------------------------------------------------------------------------------------------------------------------------
us_english      
                                                                                                                
CastedDate_US   
----------------
      2025-08-03

We got two different dates, depending on the session’s default date format. For this reason, CAST() is not recommended for production code where the session settings cannot be guaranteed to be consistent. However, if you do choose to use this method in your own session, it’s probably a good idea to explicitly set date format of your session (either by setting LANGUAGE or explicitly setting DATEFORMAT) before running the CAST() operation.

There are several ways to check the current language in SQL Server. You can also change the language quite easily, and this will affect your default date format (unless you explicitly set DATEFORMAT).

4. Using the PARSENAME() and DATEFROMPARTS() Functions

Another way to do it is to combine PARSENAME() and DATEFROMPARTS(). This method is more verbose but can be useful if you need to manipulate the individual parts of the date string before converting them. It involves splitting the string and then reassembling it into a date. This is overkill for a simple conversion but it does demonstrate another way to work with date strings.

Example:

DECLARE @mmddyyyy_string VARCHAR(10) = '08/03/2025';

SELECT DATEFROMPARTS(
    CAST(PARSENAME(REPLACE(@mmddyyyy_string, '/', '.'), 1) AS INT), -- YYYY
    CAST(PARSENAME(REPLACE(@mmddyyyy_string, '/', '.'), 3) AS INT), -- MM
    CAST(PARSENAME(REPLACE(@mmddyyyy_string, '/', '.'), 2) AS INT)  -- DD
) AS converted_date;

Result:

converted_date  
----------------
      2025-08-03

Explanation:

  • REPLACE(@mmddyyyy_string, '/', '.'): Replaces the slashes with dots, which is a requirement for PARSENAME(). The PARSENAME() function is designed to return part of an object name, using the dots as separators, but we can also use it in other scenarios (such as this one).
  • PARSENAME(..., 1): Extracts the first part (the year, since it’s at the end).
  • PARSENAME(..., 2): Extracts the second part (the day).
  • PARSENAME(..., 3): Extracts the third part (the month).
  • DATEFROMPARTS(year, month, day): A function that constructs a DATE value from integer year, month, and day components.

Summary

Here’s a quick recap:

MethodSyntaxReliabilityPerformanceBest For…
CONVERT() with Style 101CONVERT(DATE, string, 101)High (explicitly defines the format)HighThe most common approach for known input formats.
TRY_CONVERT() with Style 101TRY_CONVERT(DATE, string, 101)High (explicit format, handles errors gracefully)HighData cleansing and ETL processes where input data might be inconsistent.
CAST()CAST(string AS DATE)Low (depends on server settings)HighQuick and easy, but only if you are certain about the server’s default date format. Avoid this for production code.
DATEFROMPARTS()DATEFROMPARTS(...)HighLowSituations where you need to manipulate the date parts individually before conversion. Overkill for simple conversions.

For most scenarios, CONVERT(DATE, your_string, 101) is the best and safest option. If you are dealing with potentially bad data, TRY_CONVERT() is a great alternative that prevents query failures.