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

Converting a string in DD/MM/YYYY format to a DATE type can be done in several ways in SQL Server. This article presents five options along with examples.

Option 1: Using CONVERT() with Style 103

SELECT CONVERT(DATE, '31/07/2025', 103) AS ConvertedDate;

Output:

ConvertedDate   
----------------
      2025-07-31

Here’s an explanation:

• The CONVERT() function is used to explicitly convert an expression from one data type to another.
• The third argument (103) is the style code that tells SQL Server how to interpret the date string.
• Style 103 corresponds to the British/French date format, which is DD/MM/YYYY.
• This is one of the most reliable and efficient ways to convert a known DD/MM/YYYY format string to a DATE.

This option is ideal for when you’re sure the input will always be in DD/MM/YYYY format.

Option 2: Using CAST() with SET DATEFORMAT

SET DATEFORMAT dmy;
SELECT CAST('31/07/2025' AS DATE) AS ConvertedDate;

Result:

ConvertedDate   
----------------
      2025-07-31

How this option works:

• SQL Server uses a session-level setting called DATEFORMAT that determines how it interprets ambiguous date strings.
• By default, SQL Server may interpret a date like '31/07/2025' incorrectly, especially on U.S.-based installations where the default format is MDY.
• The SET DATEFORMAT dmy; command tells SQL Server to interpret the string as day/month/year.
• Then, the CAST() function is used to convert the string to a DATE.

This option can be useful when converting multiple date strings in a session or batch, and you want to ensure the same interpretation without specifying a style code every time.

It’s important to remember that the SET DATEFORMAT command only affects the current session or scope. So be careful about switching between clients when using this method. Here’s what can happen when the DATEFORMAT is set to a different format:

SELECT CAST('31/07/2025' AS DATE) AS ConvertedDate;

Output:

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

In some ways we’re probably lucky to get an error. I say this because there’s also the possibility of the conversion succeeding, but producing the wrong date without us even noticing. For example:

SELECT CAST('02/07/2025' AS DATE) AS ConvertedDate;

Output:

ConvertedDate   
----------------
      2025-02-07

In this case, SQL Server incorrectly produced the wrong date. We needed 2025-07-02 but got 2025-02-07. The reason this happened is because both the DD and MM values were within their limits (i.e. 31 or less for DD and 12 or less for MM). Therefore, SQL Server had no reason to produce an error. It didn’t know whether our date was formatted using MM/DD/YYYY or DD/MM/YYYY, and so it went ahead based on the session’s DATEFORMAT setting.

Option 3: Using PARSE() with Culture

SELECT PARSE('31/07/2025' AS DATE USING 'en-GB') AS ConvertedDate;

Output:

ConvertedDate   
----------------
      2025-07-31

Here’s how this one works:

• PARSE() is a function introduced in SQL Server 2012 that uses the .NET Framework to interpret strings based on culture-specific settings. This means we can override the DATEFORMAT setting on the current session, due to the fact that we’re explicitly specifying the culture when calling the function.
• 'en-GB' (English – United Kingdom) uses the DD/MM/YYYY format.
• This method allows fine-grained control over the format via culture codes, which is useful if dealing with multiple international formats.

This option can be handy when you need culture-aware parsing or when you’re dealing with dynamic date formats. However, PARSE() relies on the .NET CLR, so it’s a lot slower than CONVERT() or CAST(). Avoid using it in high-performance or bulk data scenarios.

Option 4: Using TRY_CONVERT()

SELECT TRY_CONVERT(DATE, '31/07/2025', 103) AS ConvertedDate;

Result:

ConvertedDate   
----------------
      2025-07-31

Here’s a quick rundown:

• TRY_CONVERT() works like CONVERT(), but instead of throwing an error on invalid input, it returns NULL.
• This is useful when working with data from external sources where the format may be inconsistent or when you expect some invalid values.

This can be a handy option when the input data may be invalid or in an unexpected format, and you want to avoid query failures.

Option 5: Using TRY_PARSE()

SELECT TRY_PARSE('31/07/2025' AS DATE USING 'en-GB') AS ConvertedDate;

Output:

ConvertedDate   
----------------
      2025-07-31

Breakdown:

• TRY_PARSE() is the safe version of PARSE(), returning NULL instead of error on failure.
• Like PARSE(), it uses culture info ('en-GB') to interpret the date format.

This option can be useful for when you want to parse date strings using regional formats safely, but performance is not critical. That said, this function also relies on the .NET CLR and is not efficient for large datasets or performance-sensitive applications.

Summary of Options

Here are the above options in a nutshell:

Method	Supports DD/MM/YYYY	Safe (returns NULL on error)	Culture-aware	Performance	Use Case
CONVERT(..., 103)	Yes	No	No	High	Standard, reliable conversion
CAST() + SET DATEFORMAT	Yes	No	No	High	Batch conversions with session control
PARSE(... USING 'en-GB')	Yes	No	Yes	Low	Culture-specific needs
TRY_CONVERT(..., 103)	Yes	Yes	No	High	Converting untrusted input
TRY_PARSE(... USING 'en-GB')	Yes	Yes	Yes	Low	Safe parsing with culture awareness