November 29, 2010 at 4:31 am
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
November 29, 2010 at 4:38 am
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.November 29, 2010 at 4:49 am
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
November 29, 2010 at 4:58 am
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.November 29, 2010 at 5:03 am
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. 🙁
November 29, 2010 at 5:36 am
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.November 29, 2010 at 6:22 am
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 ?
November 29, 2010 at 7:16 am
Why do you have LocaleID as Ireland in package? Is this the regional settings you have on your server too?
English (Ireland)
November 29, 2010 at 7:28 am
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.
November 29, 2010 at 10:28 am
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?
November 29, 2010 at 11:45 am
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);Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply