Got a strange one here that I hope someone can help with.
So we have a Windows server set to
UK Time zone, Region - United Kingdom - Dateformat confirmed at DD/MM/YYY
SQL Server is SQL 2019 patched up to CU8.
When I connect as a user with a Default Lanaguage of British English and run
SELECT date_format FROM sys.dm_exec_sessions WHERE session_id = @@SPID
I get back the dateformat as being DMY - which is perfect so everything is lined up for DMY.
Now the issue is I have a table loaded up with data from 30th Septemeber 2017 (thousands of rows).
So when I run the command WHERE ReportedDate < '2017-10-01' (I'm saying first of October 2017). It should pull back rows but
it doesnt, it get zero rows, I get that the date is in a format and SQL needs to figure it out, it does the year fine but it then
get the day and month the wrong way round.
If I add SET DATEFORMAT YMD at the top of my command it works as expected but I'm confused as to why it working in a YMD fashion when everything else
is set to DMY, is this a case of SQL overriding Server and windows settings and using the format of the date passed in for the WHERE clause (in this case DMY overridden by YMD).
Any help to understand this would be appreciated.