sql server convert function behave differently on different machine-CodePudding

When I run this query on one machine it works perfectly:

select convert(datetime, '2021-01-18 00:00:00.000')

But in another machine, I get an out of range error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

CodePudding user response：

That format is invalid when using certain language and dateformat settings. Example db<>fiddle and another example.

SET DATEFORMAT YDM;
SELECT CONVERT(datetime, '2021-01-18 00:00:00.000');

..or...

SET LANGUAGE BRITISH;
SELECT CONVERT(datetime, '2021-01-18 00:00:00.000');

Both yield:

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

You do not need to reinstall SQL Server to fix this, as it's not the installation that is the problem.

The real solution is to always use a safe, unambiguous format - see the links in the "Regional formats" section of Dating Responsibly.

These all succeed, for example:

SET DATEFORMAT YDM;
SELECT CONVERT(datetime, '2021-01-18T00:00:00.000');
SELECT CONVERT(datetime, '20210118 00:00:00.000');
SELECT CONVERT(datetime, '20210118');

...and...

SET LANGUAGE BRITISH;
SELECT CONVERT(datetime, '2021-01-18T00:00:00.000');
SELECT CONVERT(datetime, '20210118 00:00:00.000');
SELECT CONVERT(datetime, '20210118');

If you can't control the format of the string, then another option is to manually apply these settings first (this should work on whatever machine it's currently failing for you):

SET DATEFORMAT YMD;
SET LANGUAGE us_english;
SELECT CONVERT(datetime, '2021-01-18 00:00:00.000');

This is obviously not the optimal solution.

The issue may also be that the login connecting to the server with wrong results has their default language set to something other than the default US English (more than half of the languages SQL Server supports interpret yyyy-mm-dd as yyyy-dd-mm, for reasons I will never understand).

If this is the case, you may be able to fix this login...

ALTER LOGIN [login name] WITH DEFAULT_LANGUAGE = [us_english];

...but unless you are switching from british, this will have other consequences for them, such as language of warnings and errors, interpretation of other data like numeric, and so on. It also won't last if the login changes their setting back, and it won't affect any future logins that are created with a non-default language (or any logins that later change in the "wrong" direction).

So I still feel like fixing the way the ambiguous string is interpreted is not the answer.

In the comment you say you have code that does this:

WHERE datetime_col >= '2018-01-12'

In which case you can either:

(a) convert to datetime with the 120 style, overriding regional/language settings:

WHERE datetime_col >= CONVERT(datetime, '2018-01-12', 120)

(b) convert to date, which doesn't care about regional/language settings:

WHERE datetime_col >= CONVERT(date, '2018-01-12')

(c) I still think the best (and easiest!) answer is to simply not use an ambiguous format that requires workarounds and/or every login to be created a specific way and never override session settings:

WHERE datetime_col >= '20180112'