SQL Server not accepting YYYY-MM-DD-CodePudding

My local SQL Server 2016 setup at work decided not to accept the YMD date format after going through a reinstall. For example, the following query, that was and still is accepted in my coworkers' setups:

SELECT "id"  
FROM  test.dbo.tabEmp 
WHERE  "DateAdmission"  <= '2021-12-31' AND "DateAdmission">= '2021-12-30' `

When I try to run it I see this:

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

however, if i rewrite the dates as 2021-31-12 and 2021-12-30, in the YYYY-DD-MM format, they are accepted.

I can't really convert or format it since the sql queries in our system are numerous and done so in a way that it would be nearly impossible to. Is there something that can be done? I tried changing windows' Date format but to no avail.

CodePudding user response：

For the datetime and smalldatetime data types the format yyyy-MM-dd is not unambiguous (note that it is for the newer date and time data types). If you are not American, the date will very likely be interpreted as yyyy-dd-MM, and as there are not 31 months in the year you get an error.

For SQL Server, the formats that are unambiguous regardless of data type and language setting are yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn; ideally if you are using string literals use one of those formats as you can never get an error (unless you legitimately have an invalid date).

Otherwise you can explicitly CONVERT your value with a style code:

SELECT CONVERT(datetime, '2021-12-31', 126);

CodePudding user response：

It seems that your new DB instance picked up a new language after the reinstallation.

The current language setting determines the language used on all system messages, as well as the date/time formats to use.

The date format setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.

You can run the following statement to return the language currently being used:

SELECT @@LANGUAGE;

This will tell us what the current language is and the date format (as well as a few other things):

DBCC USEROPTIONS;

Date format is modifiable via the following statements:

SET LANGUAGE us_english;
SET DATEFORMAT YMD;

Here is a good article on the subject: How to Change the Current Date Format in SQL Server (T-SQL)