For some reason specifically only the DATEADD function is using the region format yyyy-dd-mm instead of the normal yyyy-mm-dd for everything else. I don't understand why this would happen I'm not even American.

For me DATEADD(day, 7, '2021-09-01') as a column returns a datetime of 2021-01-16 00:00:00.000 not the expected 2021-09-08 00:00:00.000.

Would anyone know if this a known issue in SSMS with the DATEADD function? I am using SQL Server Management Studio v17.9.1

CodePudding user response：

This isn't a problem with SQL Server Management Studio (SSMS), which is just an IDE for SQL Server (telling us you're using SSMS 17 is like telling us you're using Office 2016 when asked what version of Windows you're using), nor SQL Server, but your literal strings. Firstly, what you should really being using DATEADD against is a strongly typed date and time value; then you always get the value you expect.

As for your literal strings, however, the format yyyy-MM-dd is not unambiguous as the way it is interpreted differs when using the (old) datetime and smalldatetime datatypes. DATEADD (and DATEDIFF) will implicitly convert a string based data type to a datetime; and such conversion is interpreted according to your language settings (meaning your format is ambiguous).

The fix is to therefore use an unambiguous format. For SQL server these are yyyyMMdd and yyyy-MM-ddThh:mm:ss.nnnnnnn. So DATEADD(day, 7, '20210901') will return 08 September 2021 regardless of your language settings.