Date format - being overridden?

  • 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.



  • 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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply