Dateformat sometimes yyyy-mm-dd wrong ms-sql 2012-CodePudding

i have a database table with a datetime column. I use the management studio on the server to directly enter SQL commands. This happens: (Note the date time yyyy-mm-dd format)

DECLARE @DATETIME DATETIME= '1912-05-20'  <- WORKING (expected)
DECLARE @DATETIME DATETIME= '1912-20-05'  <- Not WORKING  (expected)
SELECT * FROM dbo.Messwerte WHERE 'dbo.Messwerte.datetime' = '2021-17-05'   <--  Working  (not expected)
SELECT * FROM dbo.Messwerte WHERE 'dbo.Messwerte.datetime' = '2021-05-17'   <--  Not Working (not expected)
Select * From dbo.Messwerte where DateTime between '2021/10/28' and '2021/10/29' <--  Not Working (not expected)
Select * From dbo.Messwerte where DateTime between '2021/28/10' and '2021/29/10' <-- Working (not expected)

This problem is only on my server. The yyyy-mm-dd format is sometimes rotated interpretet.

German Windows 10 Pro MS SQL Server 2018

What setting is wrong?

CodePudding user response：

Your date format needs to be one of these:

YYYYMMDD
YYYYMMDD HH:MM:SS.nnn
YYYY-MM-DDTHH:MM:SS.nnn

These will NOT work (either ever, or reliably across different languages):

YYYY-MM-DD
YYYY-DD-MM
YYYY/MM/DD
YYYY/DD/MM  
YYYYMMDDTHH:MM:SS.nnn

So your query needs to be either:

... WHERE [DateTime] > '2021-10-18T00:00:00.000';

... WHERE [DateTime] > '20211018';

Please read some of the links at Dating Responsibly.