How to Convert VARCHAR to Date in SQL Server

In this comprehensive article, I’ll walk you through everything you need to know about converting VARCHAR to DATE in SQL Server with real-time examples.

How to Convert VARCHAR to Date in SQL Server

When working with SQL Server databases, you’ll frequently encounter date information stored as text strings (VARCHAR) rather than proper DATE or DATETIME data types. This happens for various reasons:

• Legacy systems that stored dates as text
• Data imports from CSV files or Excel spreadsheets
• User inputs captured as strings
• Integration with systems that exchange date information as text

However, storing date information as VARCHAR can create significant problems:

1. Inability to perform proper date calculations or comparisons
2. Sorting issues (alphabetical sorting differs from chronological)
3. Storage inefficiency
4. Index performance degradation
5. Lack of date validation

Let’s explore the most effective methods to convert these VARCHAR values into proper DATE types in SQL Server.

Approach 1: Using CONVERT Function with Style Codes

The CONVERT function is SQL Server’s most versatile function for type conversions, including conversions from VARCHAR to DATE. Its power lies in style codes that specify the format of your input string.

Syntax

CONVERT(DATE, varchar_expression, style_code)

Common Style Codes for Date Conversion

Here are the style codes I use most frequently:

Example

Let’s say I’m working with a table of customer orders where the order date is stored as VARCHAR:

SELECT 
    Order_ID,
    Order_Name,
    -- Convert MM/DD/YYYY format
    CONVERT(DATE, Order_Date, 101) AS OrderDateConverted
FROM 
    Orders
WHERE 
    CONVERT(DATE, Order_Date, 101) >= '2023-01-01';

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

This query converts the Order_Date from VARCHAR to a proper DATE, allowing me to perform date comparisons.

Approach 2: Using CAST Function for ISO Formatted Dates

The CAST function works well for dates in the ISO format (YYYY-MM-DD), which is the recommended format for date literals in SQL Server.

Syntax

CAST(varchar_expression AS DATE)

Example

-- Works with ISO format (YYYY-MM-DD)
SELECT CAST('2023-07-24' AS DATE) AS ConvertedDate;

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

I generally prefer CAST over CONVERT when dealing with standardized ISO date formats because:

1. It follows ANSI SQL standards
2. The code is more readable
3. It’s more portable across different database systems

However, CAST doesn’t support style codes, so it’s limited to ISO format dates.

Approach 3: Using TRY_CONVERT for Error Handling

When dealing with potentially invalid date strings, TRY_CONVERT is my go-to function. It returns NULL instead of generating an error when conversion fails.

Syntax

TRY_CONVERT(DATE, varchar_expression, style_code)

Example

In my consulting work for a major retail chain in Chicago, we had to process millions of transaction records with inconsistent date formats. Here’s how I handled it:

SELECT 
    Order_ID,
    Order_Name,
    -- Try various date formats
    COALESCE(
        TRY_CONVERT(DATE, Order_Date, 101),  -- MM/DD/YYYY
        TRY_CONVERT(DATE, Order_Date, 103),  -- DD/MM/YYYY
        TRY_CONVERT(DATE, Order_Date, 112)   -- YYYYMMDD
    ) AS OrderDate
FROM 
    Orders
WHERE 
    TRY_CONVERT(DATE, Order_Date, 101) IS NOT NULL
    OR TRY_CONVERT(DATE, Order_Date, 103) IS NOT NULL
    OR TRY_CONVERT(DATE, Order_Date, 112) IS NOT NULL;

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

This approach allowed us to handle multiple date formats gracefully without errors, recovering valid dates from a messy dataset.

Approach 4: Using PARSE with Culture Information

Introduced in SQL Server 2012, the PARSE function converts strings to dates with culture awareness.

Syntax

PARSE(varchar_expression AS DATE USING culture)

Example

-- Convert using US date format
SELECT PARSE('07/24/2023' AS DATE USING 'en-US') AS USDate;

-- Convert using UK date format
SELECT PARSE('24/07/2023' AS DATE USING 'en-GB') AS UKDate;

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

While powerful, PARSE has higher performance overhead than CONVERT or CAST; therefore, I typically reserve it for situations where cultural context is crucial for accurate interpretation.

Best Practices

Below are the best practices:

1. Validate Date Strings Before Conversion

Always validate your date strings before conversion. It’s better to use TRY_CONVERT or TRY_PARSE to handle invalid dates:

UPDATE Customers
SET BirthDate = TRY_CONVERT(DATE, BirthDateString, 101)
WHERE TRY_CONVERT(DATE, BirthDateString, 101) IS NOT NULL;

2. Consider Performance Implications

Date conversion functions have different performance characteristics:

• CAST/CONVERT: Fastest performance
• TRY_CONVERT: Slightly slower than CONVERT
• PARSE/TRY_PARSE: Significantly slower (up to 4x slower in my benchmarks)

For bulk operations, prefer CONVERT or TRY_CONVERT when possible.

3. Handle Ambiguous Dates Carefully

The date format “02/03/2023” could be interpreted as February 3rd or March 2nd, depending on regional settings. Always use style codes with CONVERT to explicitly define the format:

-- Explicitly American format (MM/DD/YYYY)
SELECT CONVERT(DATE, '02/03/2023', 101);  -- February 3, 2023

4. Use Date Indexes Effectively

After converting VARCHAR to DATE, create appropriate indexes on date columns for query performance:

-- After conversion
CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate);

Video Tutorial

Conclusion

Converting VARCHAR to DATE in SQL Server is essential for database developers and administrators. By understanding the various methods as mentioned in this article, you can easily achieve this requirement.

Remember these key points:

• Use CONVERT with style codes for most conversions
• Use CAST for ISO-formatted dates
• Use TRY_CONVERT for error-resistant conversions
• Consider PARSE for culture-specific date interpretations

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.