Fixing Invalid Date Conversions in SQL Server

When you work with dates in SQL Server, you’ll often run into situations where a value can’t be converted directly to a datetime or date. This usually happens because the source data isn’t in a format SQL Server recognises, or because the value itself is out‑of‑range (e.g., “2025‑02‑30”). Fortunately, the built‑in conversion functions CAST() and CONVERT() provide us with enough flexibility to clean up those problematic values without resorting to messy string manipulation.

Below we’ll look at the most common scenarios, show how to diagnose the issue, and demonstrate how to fix it.

Why Dates Fail to Convert

When using CAST() or CONVERT() to convert a value to a date type, SQL Server expects the value to be in a specific format that enables it to interpret the date. If the incoming string deviates we’ll usually get an error:

SELECT CAST('31/12/2023' AS datetime);

Output:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

In this case, SQL Server thinks I’m trying to convert the 12 day of the 31st month and so it throws an error.

Here’s another one:

SELECT CAST('31/12' AS datetime);

Output:

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

This is a different error, but it’s still due to the value we passed. In this case the value doesn’t really resemble a date. Or more specifically, we omitted the year part of the date.

Using `CONVERT()` with Style Codes

The easiest way to overcome the first error is to use CONVERT() with a style code. The style code tells SQL Server how to interpret the string. Examples include:

Style	Format
103	`dd/mm/yyyy`
104	`dd.mm.yyyy`
105	`dd-mm-yyyy`
106	`dd mon yyyy`
112	`yyyymmdd` (ISO)

Example: Converting a British/French Date Format

Here’s an example of converting a date that uses dd/mm/yyyy format:

DECLARE @euroDate varchar(10) = '31/12/2023';
SELECT CONVERT(date, @euroDate, 103);

Result:

2023-12-31

The 103 style uses the British/French standard. This means that we’re telling SQL Server that the date value we provided is styled as dd/mm/yyyy.

If I forget the style, SQL Server falls back to my default (U.S.) and fails:

DECLARE @euroDate varchar(10) = '31/12/2023';
SELECT CONVERT(date, @euroDate);

Output:

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

Whether you get an error will depend on your session’s default DATEFORMAT setting. This setting determines how date values are interpreted, and so it’s possible to get an error with one setting and for the cast to succeed with another.

The DATEFORMAT for your session can be set either explicitly with the SET DATEFORMAT command, or implicitly with the SET LANGUAGE command.

Let’s run that second example again, but this time I’ll start by changing my session’s LANGUAGE setting (which by default, will change my DATEFORMAT value):

SET LANGUAGE British;
DECLARE @euroDate varchar(10) = '31/12/2023';
SELECT CONVERT(date, @euroDate);

Output:

2023-12-31

This time it worked. That’s because, by changing my session’s LANGUAGE to British, it also implicitly changed my DATEFORMAT setting to DMY, which is the convention used in Britain. SQL Server understood that when we passed 31/12/2023, we meant that it uses dd/mm/yyyy format. This enabled it to perform the conversion without error.

You can use the DBCC USEROPTIONS to see the current DATEFORMAT and other settings for your session.

Handling Invalid Calendar Dates

Sometimes the string looks fine but represents an impossible date, such as “2023‑02‑30”. In those cases, you need to validate before converting.

A quick way is to use TRY_CAST() or TRY_CONVERT(). These functions return NULL instead of raising an error, allowing you to filter or replace bad rows.

SET LANGUAGE us_English;
DECLARE @badDate varchar(10) = '2023-02-30';

SELECT TRY_CAST(@badDate AS date);

Output:

NULL

From there you can decide what to do:

Replace NULL with a sentinel value ('1900-01-01')
Log the offending rows for later cleanup
Apply a fallback rule (e.g., set the day to the last valid day of the month)

By the way, in case you’re wondering, changing the above example to use CAST() instead of TRY_CAST() results in the 241 error:

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

Example of Auto‑correcting to the Last Day of the Month

Here’s an example of applying a fallback rule as suggested in the third option mentioned above. In this case we’re auto‑correcting to the last day of the month:

SET LANGUAGE us_English;
DECLARE @rawDate varchar(10) = '2023-02-30';

SELECT ISNULL(
    TRY_CAST(@rawDate AS date),
    EOMONTH(DATEFROMPARTS(
        LEFT(@rawDate,4),               -- year
        SUBSTRING(@rawDate,6,2),        -- month
        1))                             -- first day of month
) AS CorrectedDate;

Result:

2023-02-28

The logic extracts the year and month, builds a date for the first day of that month, then uses EOMONTH() to get the actual last day.

Mixed Format

There are times when you need to reformat a string before casting it. Suppose you receive dates as YYYYMMDD but some rows contain hyphens (YYYY-MM-DD). You can standardise them with REPLACE() and then cast:

DECLARE @mixedDates table (raw varchar(10));
INSERT INTO @mixedDates VALUES ('20230815'), ('2023-08-16');

SELECT
    raw,
    CAST(REPLACE(raw, '-', '') AS date) AS NormalisedDate
FROM @mixedDates;

Output:

raw        NormalisedDate  
---------- ----------------
20230815         2023-08-15
2023-08-16       2023-08-16

Because the hyphens are removed, both strings now match the ISO YYYYMMDD style that CAST() understands natively.

Bulk Fixes in Real Tables

When you have a production table with a problematic column (varchar(20) BadDate), you might want to create a new, clean column and migrate data to the new column. Here’s an example of a script that walks through such a scenario:

/* --------------------------------------------------------------
    1. Create the Transactions table with a varchar column that
       holds the raw date strings.
   -------------------------------------------------------------- */
DROP TABLE IF EXISTS dbo.Transactions;
GO

CREATE TABLE dbo.Transactions
(
    TransactionID   INT IDENTITY(1,1) PRIMARY KEY,
    Description     NVARCHAR(100) NOT NULL,
    BadDate         VARCHAR(20)   NULL   -- raw date strings (mixed quality)
);
GO

/* --------------------------------------------------------------
   2.  Insert sample data.
       • Some rows are in the expected dd/mm/yyyy format.
       • Some rows use other separators or ISO format.
       • A few rows contain outright invalid dates.
   -------------------------------------------------------------- */
INSERT INTO dbo.Transactions (Description, BadDate)
VALUES
    (N'Order #1001', N'15/08/2023'),   -- valid dd/mm/yyyy
    (N'Order #1002', N'01/09/2023'),   -- valid dd/mm/yyyy
    (N'Order #1003', N'2023-09-02'),   -- ISO format (won't match style 103)
    (N'Order #1004', N'31/02/2023'),   -- invalid calendar date
    (N'Order #1005', N'12-08-2023'),   -- dd-mm-yyyy (different separator)
    (N'Order #1006', N''),            -- empty string
    (N'Order #1007', NULL),           -- NULL already
    (N'Order #1008', N'30/04/2023');   -- another valid date
GO

/* --------------------------------------------------------------
   3.  Add the new CleanDate column (date datatype, nullable).
   -------------------------------------------------------------- */
ALTER TABLE dbo.Transactions
ADD CleanDate DATE NULL;
GO

/* --------------------------------------------------------------
   4.  Populate CleanDate using TRY_CONVERT with style 103.
       Rows that can't be parsed will stay NULL.
   -------------------------------------------------------------- */
UPDATE dbo.Transactions
SET CleanDate = TRY_CONVERT(date, BadDate, 103);
GO

/* --------------------------------------------------------------
   5.  Inspect the whole table – you’ll see which rows were
       successfully converted and which remain NULL.
   -------------------------------------------------------------- */
SELECT
    TransactionID,
    Description,
    BadDate,
    CleanDate
FROM dbo.Transactions
ORDER BY TransactionID;
GO

/* --------------------------------------------------------------
   6.  Show only the rows that still have a NULL CleanDate.
       Those are the ones that need extra handling (e.g., custom
       parsing, logging, or manual correction).
   -------------------------------------------------------------- */
SELECT
    TransactionID,
    Description,
    BadDate,
    CleanDate
FROM dbo.Transactions
WHERE CleanDate IS NULL;
GO

After verifying the migration, you can drop the old column and rename the new one.

Wrapping Up

CAST() and CONVERT() are very useful, but they only work reliably when you understand the underlying format expectations. By:

Picking the right style code
Using the TRY_ variants for safety
Applying simple string fixes when necessary

you can turn a chaotic mix of date strings into clean, query‑ready date or datetime columns.

Why Dates Fail to Convert

Using CONVERT() with Style Codes

Example: Converting a British/French Date Format

Handling Invalid Calendar Dates

Example of Auto‑correcting to the Last Day of the Month

Mixed Format

Bulk Fixes in Real Tables

Wrapping Up

Using `CONVERT()` with Style Codes