January 18, 2021 at 7:31 pm
Hi all,
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.
Thanks,
Nic
January 18, 2021 at 9:55 pm
When using the ISO format YYYY-MM-DD in SQL Server on a British English language and converting (implicit or explicit) that value to a datetime data type - it will be interpreted as YYYY-DD-MM (Yes - SQL Server datetime data type is broken).
So - in your query...comparing the value '2020-10-01' to a column stored as datetime - SQL Server is implicitly converting the string to January 10th 2020 and not October 1st 2020.
If comparing the string value '2020-10-01' to a date column - SQL Server will correctly interpret that value as YYYY-MM-DD and implicitly convert it to October 1st 2020.
You can change your query to use YYYYMMDD instead - as in 20201001 - and it will be correctly interpreted by SQL Server as October 1st 2020 regardless of which date data type is utilized.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply