Troubleshooting Date Format Errors in SQL Server Imports

by

Importing data into SQL Server is usually quite straightforward. That is, until you run into date and time formatting issues. Dates that look fine in a CSV, Excel, or flat file can suddenly throw errors or, worse, silently load with the wrong values. Since SQL Server is strict about how it interprets dates, mismatches between source file formats and SQL Server’s expectations are one of the most common headaches during imports.

This article looks at why these errors happen, what SQL Server expects, and how to troubleshoot these pesky date format issues.

Why Date Formats Cause Issues

SQL Server doesn’t just “guess” a date format. It uses language and regional settings to interpret values. For instance:

  • 01/02/2024 could mean January 2, 2024 in the U.S. (MM/DD/YYYY).
  • The same string could mean February 1, 2024 in many other parts of the world (DD/MM/YYYY).

When SQL Server receives a string that doesn’t match the current session’s date format, it may fail with an error such as the 241 error:

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

In some cases it could quietly insert the wrong date. This is even worse, as it’s likely we won’t even notice the wrong date/s, especially if we’re dealing with a large amount of data.

Common Scenarios

Here are some common scenarios that could result in date issues when importing into SQL Server.

1. Importing from CSV Files

Suppose you’re importing a CSV with this row:

CustomerID, OrderDate, Amount
123, 12/03/2024, 500.00

If your SQL Server session is set to U.S. English, 12/03/2024 is December 3. If your CSV actually meant March 12, your data is now wrong without you realizing it.

2. Excel Exports

Excel adds another twist. It sometimes exports dates as serialized integers (like 45155 for 2023-08-15). If those land in SQL Server without being converted, they just become numbers with no context.

3. Mixed Formats

Even within the same file, you may see mixed formats, such as:

04-01-2024
2024/01/04
Jan 04 2024

SQL Server won’t handle this gracefully. Some rows will import, others will fail. And of course, some may import with the wrong date as already discussed.

How SQL Server Interprets Dates

SQL Server recognizes certain unambiguous formats better than others. The safest formats to use during imports are:

  • ISO 8601 date format: YYYY-MM-DD (e.g., 2024-08-15)
  • ISO 8601 datetime format: YYYY-MM-DDThh:mm:ss (e.g., 2024-08-15T14:30:00)

These are language-neutral and always interpreted consistently.

By contrast, formats like MM/DD/YYYY or DD-MM-YYYY depend on session settings.

You can check the current date format with:

DBCC USEROPTIONS;

This will show values like dateformat mdy or dateformat dmy.

Here’s what I got when I ran that:

Set Option               Value         
-----------------------  --------------
textsize                 2147483647    
language                 us_english    
dateformat               mdy           
datefirst                7             
lock_timeout             -1            
quoted_identifier        SET           
ansi_null_dflt_on        SET           
ansi_warnings            SET           
ansi_padding             SET           
ansi_nulls               SET           
concat_null_yields_null  SET           
isolation level          read committed

This shows the date format as mdy, which is the default format for the us_english language. If I were to change the language for my current session, it would update the dateformat accordingly (unless the new language uses the same date format). It’s also possible to change the dateformat value directly without changing the language.

So as you can see, there are a multitude of possible date format scenarios, and these can impact on how dates are presented in various contexts.

Fixing the Problem

The way you deal with the problem will largely depend on the situation. Here are some ideas.

Clean Data Before Import

If you control the source file, convert all dates to ISO 8601 before import. For example, instead of 03/12/2024, write 2024-03-12.

In PowerShell, you could normalize a CSV like this:

Import-Csv input.csv | ForEach-Object {
    $_.OrderDate = (Get-Date $_.OrderDate).ToString("yyyy-MM-dd")
    $_
} | Export-Csv cleaned.csv -NoTypeInformation

Use CONVERT() or TRY_CONVERT()

When importing into staging tables as text, explicitly cast dates. You can use functions like CONVERT() or TRY_CONVERT():

SELECT 
    TRY_CONVERT(date, OrderDate, 103) AS OrderDate -- 103 = dd/mm/yyyy
FROM StagingOrders;

If a row doesn’t match, TRY_CONVERT returns NULL instead of throwing an error. This may or may not be what you want. Using CONVERT() will result in an error if a date can’t be converted, which will provide you with the opportunity to fix the underlying cause, rather than allowing NULL values to sneak through.

Control Session Settings

If you know the incoming data format, you can temporarily set the session’s date format:

SET DATEFORMAT dmy; -- if source is dd/mm/yyyy

But this only helps if you’re consistent across all imports.

Stage as VARCHAR First

When dealing with messy or mixed data, one approach is to load all dates as strings into a staging table:

CREATE TABLE StagingOrders (
    CustomerID INT,
    OrderDate VARCHAR(50),
    Amount DECIMAL(10,2)
);

From there, you can run checks to see which formats exist:

SELECT DISTINCT OrderDate FROM StagingOrders;

You can then normalize the dates into a proper DATE column with TRY_CONVERT() or custom parsing logic.

Example: Normalizing Mixed Dates

lf you have this staging data:

OrderDate  
-----------
01/02/2024 
2024-02-01 
Feb 01 2024

You can standardize with:

SELECT 
    COALESCE(
        TRY_CONVERT(date, OrderDate, 101), -- mm/dd/yyyy
        TRY_CONVERT(date, OrderDate, 103), -- dd/mm/yyyy
        TRY_CONVERT(date, OrderDate, 113), -- mon dd yyyy
        TRY_CONVERT(date, OrderDate)       -- ISO
    ) AS NormalizedDate
FROM StagingOrders;

Output:

NormalizedDate          
------------------------
2024-01-02
2024-02-01
2024-02-01

This approach tests multiple formats and picks the first valid conversion. This is because the COALESCE() function returns its first non-NULL argument. And by using TRY_CONVERT(), we’re returning a NULL value whenever a conversion fails.

However, this technique is not fool-proof. It’s still possible to get the wrong date if the day is 12 or less. For example, if style 101 succeeds but you really need 103. For this reason, it pays to be extra-vigilant when converting and formatting dates in SQL Server.