Baffling DATEPART("dw"...) difference for day of week

  • Hi all,

    I've come up against a REAL head scratcher when trying to get a simple expression working as an SSIS variable.

    Here's the expression for the variable

    datepart("dw",getdate())

    On my dev PC today (Monday) the variable's value shows 1

    In a script task, the following line ...

    MsgBox(DatePart(DateInterval.Weekday, Today).ToString)

    ... on the same machine gives 2 !!!

    Does anyone have any idea what the heck is going on here ? There seems to be a difference between the VB function and the SQL function ?

    @@DateFirst on SQL is showing 7, running SQL 2008 Dev Edition on XP

  • Joseph Fallon (11/29/2010)


    I've come up against a REAL head scratcher when trying to get a simple expression working as an SSIS variable.

    Here's the expression for the variable

    datepart("dw",getdate())

    On my dev PC today (Monday) the variable's value shows 1

    In a script task, the following line ...

    MsgBox(DatePart(DateInterval.Weekday, Today).ToString)

    ... on the same machine gives 2 !!!

    Does anyone have any idea what the heck is going on here ? There seems to be a difference between the VB function and the SQL function ?

    @@DateFirst on SQL is showing 7, running SQL 2008 Dev Edition on XP

    By default VB uses FirstDayOfWeek == Sunday, if you want to use Monday as the first day of the week just set FirstDayOfWeek.Monday

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Hi Pablo,

    Thanks for the post.

    I'm not too worried about the VB side of things ... all I need is for the Expression that I use for my variable to be consistent.

    On some servers it gives 1 whereas on others it gives 2.

    The VB code always consistently gives 2 on all machines.

    Many Thanks,

    Joe

  • Joseph Fallon (11/29/2010)


    Hi Pablo,

    Thanks for the post.

    I'm not too worried about the VB side of things ... all I need is for the Expression that I use for my variable to be consistent.

    On some servers it gives 1 whereas on others it gives 2.

    The VB code always consistently gives 2 on all machines.

    If the "issue" is on SQL Server side check the value set by "SET DATEFIRST", by default SQL Server uses Sunday as the first day of the week - I bet a buck offending server - the one showing Monday=1 - has SET DATEFIRST value set to 1, which is Monday.

    Devil is in the details, you have to check setup on servers.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • PaulB-TheOneAndOnly (11/29/2010)


    Joseph Fallon (11/29/2010)


    Hi Pablo,

    Thanks for the post.

    I'm not too worried about the VB side of things ... all I need is for the Expression that I use for my variable to be consistent.

    On some servers it gives 1 whereas on others it gives 2.

    The VB code always consistently gives 2 on all machines.

    If the "issue" is on SQL Server side check the value set by "SET DATEFIRST", by default SQL Server uses Sunday as the first day of the week - I bet a buck offending server - the one showing Monday=1 - has SET DATEFIRST value set to 1, which is Monday.

    Devil is in the details, you have to check setup on servers.

    Hope this helps.

    Thanks again - I'm afraid

    SELECT @@DATEFIRST

    returns 7 in SSMS on all servers. 🙁

  • Joseph Fallon (11/29/2010)


    PaulB-TheOneAndOnly (11/29/2010)


    Joseph Fallon (11/29/2010)


    Hi Pablo,

    Thanks for the post.

    I'm not too worried about the VB side of things ... all I need is for the Expression that I use for my variable to be consistent.

    On some servers it gives 1 whereas on others it gives 2.

    The VB code always consistently gives 2 on all machines.

    If the "issue" is on SQL Server side check the value set by "SET DATEFIRST", by default SQL Server uses Sunday as the first day of the week - I bet a buck offending server - the one showing Monday=1 - has SET DATEFIRST value set to 1, which is Monday.

    Devil is in the details, you have to check setup on servers.

    Hope this helps.

    Thanks again - I'm afraid

    SELECT @@DATEFIRST

    returns 7 in SSMS on all servers. 🙁

    ... and, what do you get if you do datepart("dw",getdate()) on each one of them?

    I'll suggest to write a query that shows both DATEFIRST and datepart("dw"... if you see no consistency, open a ticket with Microsoft 🙂

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • PaulB-TheOneAndOnly (11/29/2010)


    Joseph Fallon (11/29/2010)


    PaulB-TheOneAndOnly (11/29/2010)


    Joseph Fallon (11/29/2010)


    Hi Pablo,

    Thanks for the post.

    I'm not too worried about the VB side of things ... all I need is for the Expression that I use for my variable to be consistent.

    On some servers it gives 1 whereas on others it gives 2.

    The VB code always consistently gives 2 on all machines.

    If the "issue" is on SQL Server side check the value set by "SET DATEFIRST", by default SQL Server uses Sunday as the first day of the week - I bet a buck offending server - the one showing Monday=1 - has SET DATEFIRST value set to 1, which is Monday.

    Devil is in the details, you have to check setup on servers.

    Hope this helps.

    Thanks again - I'm afraid

    SELECT @@DATEFIRST

    returns 7 in SSMS on all servers. 🙁

    ... and, what do you get if you do datepart("dw",getdate()) on each one of them?

    I'll suggest to write a query that shows both DATEFIRST and datepart("dw"... if you see no consistency, open a ticket with Microsoft 🙂

    The following query

    SELECT @@DATEFIRST,datename(dw,GETDATE()), DATEPART(dw,GETDATE())

    is consistent across servers giving

    7, Monday, 2

    It's just the value of the variable that changes.

    I'm uploading a dummy test package so you can see - just rename the file.

    What's the variable value on your machine please ?

  • Why do you have LocaleID as Ireland in package? Is this the regional settings you have on your server too?

    English (Ireland)

  • Amu (11/29/2010)


    Why do you have LocaleID as Ireland in package?

    English (Ireland)

    The server's located in Ireland - as am I.

    Is this the regional settings you have on your server too?

    Good question !

    Yes, all servers are set to this Locale

    From testing on my own PC, changing this setting does not seem to have any affect on the variable value in the package.

  • The following query

    SELECT @@DATEFIRST,datename(dw,GETDATE()), DATEPART(dw,GETDATE())

    is consistent across servers giving

    7, Monday, 2

    It's just the value of the variable that changes.

    That looks like what you want ... in the original post "DATEPART(dw,GETDATE())" was returning "1" if I'm not mistaken. Out of curiosity, do the SQL getdate() and VB now() match?

  • I suspect a language setting on the user that your SSIS package is using to log in to SQL server.

    Probably you are using a different login in SSMS than SSIS and one of the logins is using us_english and one is using british (I know you would not choose British, but maybe someone else did!).

    Try this to see what I mean...

    set language us_english

    select @@LANGUAGE as language, @@DATEFIRST as datefirst, DATEPART(dw,getdate()) as datepart

    set language british

    select @@LANGUAGE as language, @@DATEFIRST as datefirst, DATEPART(dw,getdate()) as datepart

    The first one returns the DAYOFWEEK as 2 for today (29th November 2010) and the second returns 1.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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