Convert dd/mm/yyyy to date SQL Server

In this comprehensive article, I’ll guide you through multiple approaches for converting dd/mm/yyyy to date in SQL Server correctly, and provide you with best practices for working with dates in SQL Server.

Convert dd/mm/yyyy to date SQL Server

Before diving into the solutions, let’s understand why proper date conversion is crucial. SQL Server stores dates in a standardized internal format; however, it must correctly interpret string inputs. When you have strings like ’25/12/2023′ (Christmas Day), SQL Server might attempt to interpret this as the 25th day of the 12th month, which doesn’t exist, resulting in errors or incorrect data.

Approach 1: Using CONVERT Function with Style Parameters

The CONVERT function is one of the most versatile tools in SQL Server for handling date conversions. It allows you to specify a style parameter that tells SQL Server how to interpret the input string.

For converting dd/mm/yyyy strings, style 103 is the best:

SELECT CONVERT(date, '16/07/2025', 103) AS ConvertedDate;

This tells SQL Server to interpret the string as a UK/French date format (day/month/year).

Common Style Parameters for Date Conversion

Here’s a quick reference table of useful style parameters:

The default format in SQL Server is yyyy-mm-dd, which is the unambiguous ISO standard format that works reliably across different SQL Server versions and settings.

Approach 2: Using TRY_CONVERT for Error Handling

One challenge with the CONVERT function is that it throws an error if the conversion fails. A more robust approach is to use TRY_CONVERT, which returns NULL instead of crashing your query:

SELECT 
    DOB,
    TRY_CONVERT(date, DOB, 103) AS ConvertedDate
FROM StudentDetail;

After executing the above query, I got the expected output as shown in the screenshot below.

This is particularly useful when processing large datasets, as some date strings may be malformed.

Approach 3: Using TRY_PARSE with Culture Information

SQL Server 2012 and later versions offer another powerful function: TRY_PARSE. This function allows you to specify a culture parameter to handle regional date formats:

SELECT TRY_PARSE('16/07/2025' AS date USING 'fr-FR') AS ParsedDate;

The advantage of TRY_PARSE is that it can handle culture-specific formats more naturally. Here, ‘fr-FR’ indicates French formatting (day first), which is what we need for dd/mm/yyyy strings.

Approach 4: String Manipulation and Standard Conversion

If you’re working with an older version of SQL Server that doesn’t support the above methods, you can manually rearrange the string components:

DECLARE @DateString varchar(10) = '16/07/2025';

SELECT CONVERT(date, 
       SUBSTRING(@DateString, 7, 4) + '-' + 
       SUBSTRING(@DateString, 4, 2) + '-' + 
       SUBSTRING(@DateString, 1, 2)
) AS ConvertedDate;

After executing the above query, I got the expected output as shown in the screenshot below.

This approach works by extracting the year, month, and day parts and rearranging them into the yyyy-mm-dd format that SQL Server recognizes by default.

Handling Various dd/mm/yyyy Format Variations

In real-world scenarios, you’ll encounter various separators and formats. Here’s how to handle them:

Converting dd-mm-yyyy Format

SELECT CONVERT(date, '16/07/2025', 105) AS ConvertedDate;

Converting dd.mm.yyyy Format

SELECT CONVERT(date, '31.01.2023', 104) AS ConvertedDate;

Converting Without Separators (ddmmyyyy)

DECLARE @DateString varchar(8) = '16072025';

SELECT CONVERT(date, 
       SUBSTRING(@DateString, 5, 4) + '-' + 
       SUBSTRING(@DateString, 3, 2) + '-' + 
       SUBSTRING(@DateString, 1, 2)
) AS ConvertedDate;

After executing the above query, I got the expected output as shown in the screenshot below.

Common Issues and How to Avoid Them

1. Language and Regional Settings

SQL Server’s behavior can be affected by language and regional settings. Always use explicit style parameters instead of relying on implicit conversion:

-- Problematic (depends on server settings)
SELECT CAST('31/01/2023' AS date);

-- Robust (explicit format specification)
SELECT CONVERT(date, '16/07/2025', 103);

After executing the above query, I got the expected output as shown in the screenshot below.

2. Two-Digit Years

When working with two-digit years (e.g., ’31/01/23′), SQL Server applies a century cutoff rule:

• Years 00-49 are interpreted as 2000-2049
• Years 50-99 are interpreted as 1950-1999

To avoid ambiguity, always use four-digit years when possible.

3. Handling Time Components

If your string contains time information, you might want to preserve or discard it using the query below.

-- Convert to date (discards time)
SELECT CONVERT(date, '16/07/2025 14:30:45', 103);

-- Convert to datetime (preserves time)
SELECT CONVERT(datetime, '16/07/2025 14:30:45', 103);

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server offers multiple datetime formats depending on your needs.

Displaying Dates in dd/mm/yyyy Format

It’s important to understand that SQL Server doesn’t store dates in any specific “format” internally. The format only comes into play when converting to/from strings.

If you need to display dates in dd/mm/yyyy format:

-- Using CONVERT to display as dd/mm/yyyy
SELECT CONVERT(varchar, GETDATE(), 103) AS FormattedDate;

-- Using FORMAT function (SQL Server 2012 and later)
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy') AS FormattedDate;

After executing the above query, I got the expected output as shown in the screenshot below.

The FORMAT function offers more flexibility but may have performance implications for large datasets.

Best Practices

1. Store dates using proper date types (date, datetime, datetime2) rather than strings.
2. Use parameterized queries when accepting date inputs from users.
3. Apply explicit conversion with style parameters rather than implicit conversion.
4. Use TRY_CONVERT or TRY_PARSE for robust error handling.
5. Consider using the FORMAT function only for display purposes, not in WHERE clauses or joins.

Performance Considerations

When working with large datasets, be aware that string manipulation and complex conversions can impact performance. Some tips:

• Use indexed date columns instead of converting strings in WHERE clauses
• Consider materializing converted values in a computed column
• Use the simplest conversion method that meets your needs
• Batch process significant updates that require date conversion

Conclusion

Converting dd/mm/yyyy strings to proper date formats in SQL Server is a common requirement. The CONVERT function with style 103 is the most straightforward approach for most scenarios, while TRY_PARSE with culture information offers additional flexibility for internationalization.

Remember that the best practice is to store dates using proper date data types and only convert to/from strings when necessary for input or display purposes. By following the approaches mentioned in this article, you can easily achieve this requirement.

You may also like the following articles.

• SQL Server Short Date
• SQL Server Weekday From Date
• SQL Server Date Formatting
• SQL Server Date Format 101

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.