Post reply

Different resultsets for Windows authentication and SQL login

  • February 4, 2016 at 4:19 am

    #324004

    Hi,

    I have a question with regards to the calculation of the week number in SQL.

    When I run the query on windows authentication, it calculates the start of the week as Monday but when running the same query based on SQL login account, the first day of the week changes to Sunday and I get different resultsets for both.

    Can anybody please suggest something to rectify this issue ?

    Thanks.

  • February 4, 2016 at 4:34 am

    #1856482

    I am not sure but I guess it's due to the setting of the international locale in Windows. For the Windows account it will take the setting from this account and for a SQL account it will take the setting of the SQL Server service account.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • February 4, 2016 at 12:51 pm

    #1856655

    From the Books Online documentation for the DATEPART function:

    "When datepart is week (wk, ww) or weekday (dw), the return value depends on the value that is set by using SET DATEFIRST."

    Many SET options are set implicitly by the tool used to connect and/or based on (server-)login and (database-)user settings.

    You can always override them by explicitly using SET DATEFIRST.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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