Working with dates in SQL Server is usually quite straightforward. There’s a good range of date types and functions that we can use to manipulate date/time values.
But international date formats can undo all that simplicity in a heartbeat. Something as simple as casting a string into a DATETIME type can blow up depending on how the server interprets the input. This often happens when you’re dealing with applications or imports that don’t stick to a single culture or regional setting.
Let’s walk through an example and see why SQL Server behaves this way, and more importantly, how to handle it correctly.
Why the Problem Happens
When you cast a string to DATETIME, SQL Server has to parse it. The parsing depends on the language and date format settings of the session. For example, 03/04/2025 could mean April 3rd, 2025 in the US (MM/DD/YYYY) but March 4th, 2025 in many other locales (DD/MM/YYYY). That ambiguity is a recipe for data issues.
The safest approach is to either:
- Use unambiguous formats like
YYYYMMDD(no separators), or - Use
CONVERT()/TRY_CONVERT()with an explicit style code that matches the format.
Let’s see this in practice.
Example
Here’s a script that creates a table and inserts some test strings with different date formats:
-- Create a table
DROP TABLE IF EXISTS dbo.DateFormatTest;
GO
CREATE TABLE dbo.DateFormatTest
(
Id INT IDENTITY PRIMARY KEY,
DateString NVARCHAR(20)
);
GO
-- Insert dates in different regional formats
INSERT INTO dbo.DateFormatTest (DateString)
VALUES
('03/04/2025'), -- ambiguous: could be 3rd April or 4th March
('2025-04-03'), -- ISO-style, unambiguous
('04-03-2025'), -- ambiguous, often fails
('20250403'); -- ISO basic, unambiguous
GOHere’s what happens when we try to cast the date strings as DATETIME:
SELECT
Id,
DateString,
CAST(DateString AS DATETIME) AS CastedDate
FROM dbo.DateFormatTest;Result:
Id DateString CastedDate
----------- -------------------- -----------------------
1 03/04/2025 2025-03-04 00:00:00.000
2 2025-04-03 2025-04-03 00:00:00.000
3 04-03-2025 2025-04-03 00:00:00.000
4 20250403 2025-04-03 00:00:00.000
Seems to work OK, right? Well that depends.
The above result can be misleading if you’re not aware of how international date formats work in SQL Server. This result doesn’t reflect the full story.
The above query used my own system’s SET DATEFORMAT and SET LANGUAGE settings to determine how to interpret the date strings that were passed to the CAST() function. This is fine if we know what we’re doing and we’re completely aware of the format of the incoming dates. But if we don’t and if we’re not, we could be in big trouble.
For example, if our session uses us_english, we’ll get a different result than if our session is set to British.
To demonstrate this, let’s run the query a couple more times. But this time we’ll switch language between queries:
-- Set language to us_english
SET LANGUAGE us_english;
GO
SELECT
DateString,
CAST(DateString AS DATETIME) AS US_English_Cast
FROM dbo.DateFormatTest;
GO
-- Switch to British English
SET LANGUAGE British;
GO
SELECT
DateString,
CAST(DateString AS DATETIME) AS British_Cast
FROM dbo.DateFormatTest;
GOResult:
Changed language setting to us_english.
DateString US_English_Cast
-------------------- -----------------------
03/04/2025 2025-03-04 00:00:00.000
2025-04-03 2025-04-03 00:00:00.000
04-03-2025 2025-04-03 00:00:00.000
20250403 2025-04-03 00:00:00.000
Changed language setting to British.
DateString British_Cast
-------------------- -----------------------
03/04/2025 2025-04-03 00:00:00.000
2025-04-03 2025-03-04 00:00:00.000
04-03-2025 2025-03-04 00:00:00.000
20250403 2025-04-03 00:00:00.000
As you can see, the results are different depending on our session’s language setting.
By default, when we set the language, it implicitly sets the DATEFORMAT to the default value for that language. So if we don’t explicitly set DATEFORMAT, it will use the default value for our language. It’s possible to override this behavior by setting DATEFORMAT explicitly. Regardless, the result of our CAST() will depend on the DATEFORMAT value.
A Safer Approach
We also have the option of explicitly specifying the date format when we do the conversion. The CONVERT() and TRY_CONVERT() functions allow us to specify the format of the input date format:
SELECT
Id,
DateString,
TRY_CONVERT(DATETIME, DateString, 103) AS BritishFormat, -- dd/MM/yyyy
TRY_CONVERT(DATETIME, DateString, 101) AS USFormat, -- MM/dd/yyyy
TRY_CONVERT(DATETIME, DateString, 112) AS ISOFormat -- yyyymmdd
FROM dbo.DateFormatTest;
GOResult:
Id DateString BritishFormat USFormat ISOFormat
----------- -------------------- ----------------------- ----------------------- -----------------------
1 03/04/2025 2025-04-03 00:00:00.000 2025-03-04 00:00:00.000 NULL
2 2025-04-03 2025-03-04 00:00:00.000 2025-04-03 00:00:00.000 NULL
3 04-03-2025 2025-03-04 00:00:00.000 2025-04-03 00:00:00.000 NULL
4 20250403 2025-04-03 00:00:00.000 2025-04-03 00:00:00.000 2025-04-03 00:00:00.000
So we can see that the output date depends on how the input date was translated. When we specified 103, we got a different result than the result returned with 101. Same with 112.
Best Practices
If you’re importing or dealing with international data, a few habits will save you headaches:
- Prefer ISO 8601 formats (
YYYY-MM-DDorYYYYMMDD) when generating data for SQL Server. - Use
TRY_CONVERT()with a style code when you know the format of your incoming strings. - Avoid relying on session defaults (
SET LANGUAGE,SET DATEFORMAT) unless you truly control every connection.
Wrapping Up
Dealing with dates across regions in SQL Server is less about technical limitations and more about ambiguity. A string like 03/04/2025 only makes sense if you know the context, and SQL Server will happily interpret it based on whatever session settings happen to be in place.
That’s risky. A safer approach is to standardize on ISO formats when possible and use CONVERT() or TRY_CONVERT() with explicit style codes when you can’t.
Being intentional about how you handle dates ensures your queries behave the same way everywhere, no matter who runs them or where the data comes from.