Post reply

SQL 2016 Upgrade - One user has a different datefirst value to everyone else.

  • May 22, 2017 at 7:30 am

    #342937

    Hmmmm... This is strange - I have one user in SQL 2016 which is consistently evaluating the following query as if Datefirst is 1.

    SELECT DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE))

    SELECT @@datefirst returns 7.

    We've tested it with all the other users and it's consistent with this user only.

    I've hacked it by setting datefirst in the queries for now, but I'm having trouble finding any details about this behaviour.

    Anybody seen this before?

    Thanks

    Rich

  • May 22, 2017 at 7:36 am

    #1943557

    Sounds like the Login is set to a different language. You can check with the following sql (replacing the string with the real logins):
    USE master;
    GO
    SELECT name, language
    FROM sys.syslogins
    WHERE name IN ('[Your Login]','[Other login]');

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 22, 2017 at 8:37 am

    #1943568

    Right, thanks Thom, that explains a lot. I wonder if I can pick your brains a little more? I'm in a situation where I'm having to "manage" the guys configuring the server without a sysadmin password, it's quite annoying but I can't check very much and they're not overly dynamic.....

    Do you know off hand if there is a setting on the server which defaults new user accounts to a certain language? The @@datefirst setting on the server is still US (7), so I'm assuming new users would be US by default and someone has explicitly changed this - this is a port from 2014 and essentially I need to ensure the defaults are set to the same standards as the old db.

  • May 22, 2017 at 8:49 am

    #1943570

    I think it depends on the default language for the server, which you can find in sys.configurations.
    SELECT l.name, l.alias
    FROM sys.configurations c
    JOIN sys.syslanguages l ON c.value_in_use = l.langid
    WHERE c.name = 'default language'

    John

  • May 22, 2017 at 8:55 am

    #1943573

    At creation a login's default language will default to that of the server's, however, you can also change the default language at login creation. For example, if the Server's Default language is set to English (United States) and a Login is created with its default language set to default, its default language will be English (United States).

    A LOGIN's default language can be changed by using ALTER LOGIN. For example the following changes a Login's default language to British:
    ALTER LOGIN [YourLogin] DEFAULT LANGUAGE = british;
    You can change to server's     default language using sp_configure. For example the following changes the Server's default language to British English:
    EXEC sp_configure 'default language', 23;
    GO
    RECONFIGURE
    GO
    Changing the server's default language does not change the default language of any existing logins (including those that were created with their default language being the server's default language).

    Both should be used carefully, as the default language does change how the results of some functions or how data may be read, as you have seen.

    Edit: typo fixing!

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 22, 2017 at 9:41 am

    #1943585

    Thanks guys - that's solved a problem. Appreciated 😉

Viewing 6 posts - 1 through 5 (of 5 total)

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