SQL Server not accepting YYYY-MM-DD

Question

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.

Is this against a SQL instance that's running locally on your setup? — motosubatsu, Dec 16 '21 at 15:07
In that case, Sql Server picked up the local system's cultural date/time settings, which is different from that of your co-workers. — Joel Coehoorn, Dec 16 '21 at 15:10
The YYYY-MM-DD format is not safe. It can interpret the values incorrectly based on the language setting of your database. The ONLY safe format is YYYYMMDD, that format will always work correctly. — Sean Lange, Dec 16 '21 at 15:40
Check out this demonstration from Aaron Bertrand. https://www.mssqltips.com/sqlservertutorial/9315/recommended-sql-server-date-formats/ — Sean Lange, Dec 16 '21 at 15:42
@SeanLange, I know it isn't but that is the least of the bad habits at that company, and since it isn't really feasible to change how all the different classes format their dates after years of bad habits, i would need to change how SQL server interprets them, and only i am having this issue on my team — Lestaf Santiago, Dec 16 '21 at 17:43

score 4 · Answer 1 · answered Dec 16 '21 at 14:35

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);

Yitzhak Khabinsky · Accepted Answer · 2021-12-16T19:58:35.103

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)

It is also possible to modify SQL Server instance default language globally, once and for all: How to change default language for SQL Server?

Then is there a system language that formats it to ymd? I also can't use the SET command on every transaction, since the queries are automatically run by JDBC... — Lestaf Santiago, Dec 16 '21 at 17:41
Just to add to that, what matters is the security\logons user language that matters, i literally changed every language setting on my system before ever thinking that the DBs user would matter — Lestaf Santiago, Dec 16 '21 at 18:47

SQL Server not accepting YYYY-MM-DD

2 Answers2