Post reply

@@datefirst gives different result between Excel and SSMS

  • October 3, 2011 at 10:29 am

    #249240

    I've just noticed something here - when I execute print@@datefirst on a particular database on SQL server it returns 7 to me.

    When I execute it in EXcel through the sql query pivot table window it gives me back 1.

    I am definetly connecting to the same database. anyone come across this before?

  • October 3, 2011 at 12:42 pm

    #1389748

    @@datefirst uses the language settings to determine what the customary first day of the week is. Simply changing the language setting (or having it be different in your connection than the default on your server) can affect it.

    This is directly from the BOL entry about @@datefirst

    In the following example, the language is first set to Italian. The statement SELECT @@DATEFIRST; returns 1. The language is then set to us_english. The statement SELECT @@DATEFIRST; returns 7.

    SET LANGUAGE Italian;

    GO

    SELECT @@DATEFIRST;

    GO

    SET LANGUAGE us_english;

    GO

    SELECT @@DATEFIRST;

    Note: Language is one of the optional elements you can set while setting up a connection (see the ODBC connection wizard on the 4th page).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • October 4, 2011 at 2:36 am

    #1389995

    thanks - what I don't understand is that I'm connecting to the same database, just using 2 different ways. I'm not setting the language to anything in my connection or my query. Wouldn't the language settings be the same once your on the database - or do you reckon that in the Excel example its taking the language setting from the Excel settings.

  • October 4, 2011 at 3:13 am

    #1390008

    setting the language at the top of the query in Excel fixes this - but just think its strange that the language settings in Excel overrides the SQLServer Databases settings

    thanks for the help

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

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