|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
DATEPART Gives different answers on different machines?!Hi guys,
I've noticed that the following line of T-SQL: SELECT DATEPART(dw, GETDATE()) ....when fired on different servers, gives different answers. For instance, as I write this, it's Monday. One server returned 1, and the other 2! The difference occurred when the T-SQL was run through Query Analyser, and as part of a stored procedure. Why is this?! Does each SQL Server have a setting where the first day of the week is set? Thanks, Steve. Hello Steve,
> I've noticed that the following line of T-SQL: You might also want to check that the date and times on both machines are > SELECT DATEPART(dw, GETDATE()) > ...when fired on different servers, gives different answers. For > instance, as I write this, it's Monday. One server returned 1, and the > other 2! The difference occurred when the T-SQL was run through Query > Analyser, and as part of a stored procedure. in sync. :) --- Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Thanks Kent! Actually, that was the first thing I checked. The dates are
identical and equal to today! Show quote "Kent Tegels" wrote: > Hello Steve, > > > I've noticed that the following line of T-SQL: > > SELECT DATEPART(dw, GETDATE()) > > ...when fired on different servers, gives different answers. For > > instance, as I write this, it's Monday. One server returned 1, and the > > other 2! The difference occurred when the T-SQL was run through Query > > Analyser, and as part of a stored procedure. > > You might also want to check that the date and times on both machines are > in sync. :) > > --- > Thanks, > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ > > > Hi
You could use DATEFIRST. See the below example. SET DATEFIRST 7 -- Sunday (This is default for US) go select datepart(week,GETDATE()) Thanks Hari SQL Server MVP Show quote "Steve Barker" <stevebarker@nospam.nospam> wrote in message news:D5056037-F24F-419A-A748-3248EC7AD882@microsoft.com... > Hi guys, > > I've noticed that the following line of T-SQL: > > SELECT DATEPART(dw, GETDATE()) > > ...when fired on different servers, gives different answers. For instance, > as I write this, it's Monday. One server returned 1, and the other 2! The > difference occurred when the T-SQL was run through Query Analyser, and as > part of a stored procedure. > > Why is this?! Does each SQL Server have a setting where the first day of > the > week is set? > > Thanks, > > Steve. > > I've got to the bottom of this now. When the OS on server 1 was installed, it
was set to English U.S. In the U.S., the week starts on Monday (I think). Server 2 was installed as England U.K. We use Sunday! Now, when SQL Server is installed, it defaults to the StartDay as given by the OS, so the SQL Servers were different from the off! So, of course, I should have used SET DATEFIRST to make sure everything was in-sync, but I didn't release it was even possible for the day codes to be different! You live in learn... However, this all smacks of "magic number" programming to me, and now that I'm aware of this little problem, I'm always going to use DATENAME(dw, GETDATE()), which returns 'Monday' on a Monday. Since this is an in-house system, I KNOW that it will never be used in France, for example, so I'll never have to worry about "mardi", although in truth, that's how I'm feeling right now! Show quote "Hari Prasad" wrote: > Hi > > You could use DATEFIRST. See the below example. > > > SET DATEFIRST 7 -- Sunday (This is default for US) > go > select datepart(week,GETDATE()) > > Thanks > Hari > SQL Server MVP > > > "Steve Barker" <stevebarker@nospam.nospam> wrote in message > news:D5056037-F24F-419A-A748-3248EC7AD882@microsoft.com... > > Hi guys, > > > > I've noticed that the following line of T-SQL: > > > > SELECT DATEPART(dw, GETDATE()) > > > > ...when fired on different servers, gives different answers. For instance, > > as I write this, it's Monday. One server returned 1, and the other 2! The > > difference occurred when the T-SQL was run through Query Analyser, and as > > part of a stored procedure. > > > > Why is this?! Does each SQL Server have a setting where the first day of > > the > > week is set? > > > > Thanks, > > > > Steve. > > > > > > > One way that will work in the US, UK, and France is to take advantage of the
fact that January 1, 1900 was a Monday, and is also day 0 in SQL Server datetimes. So you can use the number of days since then modulo 7 to determine the day of the week, e.g., If DateDiff(dd,0,GetDate()) % 7 = 0 Print 'It is Monday' Else Print 'It is not Monday' Tom Show quote "Steve Barker" <stevebarker@nospam.nospam> wrote in message news:DBD66596-30A8-4ED2-A4AE-92063558291C@microsoft.com... > I've got to the bottom of this now. When the OS on server 1 was installed, > it > was set to English U.S. In the U.S., the week starts on Monday (I think). > Server 2 was installed as England U.K. We use Sunday! Now, when SQL Server > is > installed, it defaults to the StartDay as given by the OS, so the SQL > Servers > were different from the off! > > So, of course, I should have used SET DATEFIRST to make sure everything > was > in-sync, but I didn't release it was even possible for the day codes to be > different! You live in learn... > > However, this all smacks of "magic number" programming to me, and now that > I'm aware of this little problem, I'm always going to use DATENAME(dw, > GETDATE()), which returns 'Monday' on a Monday. Since this is an in-house > system, I KNOW that it will never be used in France, for example, so I'll > never have to worry about "mardi", although in truth, that's how I'm > feeling > right now! > > "Hari Prasad" wrote: > >> Hi >> >> You could use DATEFIRST. See the below example. >> >> >> SET DATEFIRST 7 -- Sunday (This is default for US) >> go >> select datepart(week,GETDATE()) >> >> Thanks >> Hari >> SQL Server MVP >> >> >> "Steve Barker" <stevebarker@nospam.nospam> wrote in message >> news:D5056037-F24F-419A-A748-3248EC7AD882@microsoft.com... >> > Hi guys, >> > >> > I've noticed that the following line of T-SQL: >> > >> > SELECT DATEPART(dw, GETDATE()) >> > >> > ...when fired on different servers, gives different answers. For >> > instance, >> > as I write this, it's Monday. One server returned 1, and the other 2! >> > The >> > difference occurred when the T-SQL was run through Query Analyser, and >> > as >> > part of a stored procedure. >> > >> > Why is this?! Does each SQL Server have a setting where the first day >> > of >> > the >> > week is set? >> > >> > Thanks, >> > >> > Steve. >> > >> > >> >> >> Ah! Cunning! Thanks Tom.
Show quote "Tom Cooper" wrote: > One way that will work in the US, UK, and France is to take advantage of the > fact that January 1, 1900 was a Monday, and is also day 0 in SQL Server > datetimes. So you can use the number of days since then modulo 7 to > determine the day of the week, e.g., > > If DateDiff(dd,0,GetDate()) % 7 = 0 > Print 'It is Monday' > Else > Print 'It is not Monday' > > Tom > > "Steve Barker" <stevebarker@nospam.nospam> wrote in message > news:DBD66596-30A8-4ED2-A4AE-92063558291C@microsoft.com... > > I've got to the bottom of this now. When the OS on server 1 was installed, > > it > > was set to English U.S. In the U.S., the week starts on Monday (I think). > > Server 2 was installed as England U.K. We use Sunday! Now, when SQL Server > > is > > installed, it defaults to the StartDay as given by the OS, so the SQL > > Servers > > were different from the off! > > > > So, of course, I should have used SET DATEFIRST to make sure everything > > was > > in-sync, but I didn't release it was even possible for the day codes to be > > different! You live in learn... > > > > However, this all smacks of "magic number" programming to me, and now that > > I'm aware of this little problem, I'm always going to use DATENAME(dw, > > GETDATE()), which returns 'Monday' on a Monday. Since this is an in-house > > system, I KNOW that it will never be used in France, for example, so I'll > > never have to worry about "mardi", although in truth, that's how I'm > > feeling > > right now! > > > > "Hari Prasad" wrote: > > > >> Hi > >> > >> You could use DATEFIRST. See the below example. > >> > >> > >> SET DATEFIRST 7 -- Sunday (This is default for US) > >> go > >> select datepart(week,GETDATE()) > >> > >> Thanks > >> Hari > >> SQL Server MVP > >> > >> > >> "Steve Barker" <stevebarker@nospam.nospam> wrote in message > >> news:D5056037-F24F-419A-A748-3248EC7AD882@microsoft.com... > >> > Hi guys, > >> > > >> > I've noticed that the following line of T-SQL: > >> > > >> > SELECT DATEPART(dw, GETDATE()) > >> > > >> > ...when fired on different servers, gives different answers. For > >> > instance, > >> > as I write this, it's Monday. One server returned 1, and the other 2! > >> > The > >> > difference occurred when the T-SQL was run through Query Analyser, and > >> > as > >> > part of a stored procedure. > >> > > >> > Why is this?! Does each SQL Server have a setting where the first day > >> > of > >> > the > >> > week is set? > >> > > >> > Thanks, > >> > > >> > Steve. > >> > > >> > > >> > >> > >> > > > A very bright approach to the problem, except it may be too cunning for the
next person who maintains the code to understand. The simpler the code, the fewer maintenance problems you will have later on. Of course, if SQL Server extends the date datatype to span a longer period of time in future releases, this code will suddenly start returning 0 for Wednesday instead of Monday. Show quote "Steve Barker" <stevebarker@nospam.nospam> wrote in message news:F38E0326-4E98-4F9B-8EF8-083713AEB70F@microsoft.com... > Ah! Cunning! Thanks Tom. > > "Tom Cooper" wrote: > > > One way that will work in the US, UK, and France is to take advantage of the > > fact that January 1, 1900 was a Monday, and is also day 0 in SQL Server > > datetimes. So you can use the number of days since then modulo 7 to > > determine the day of the week, e.g., > > > > If DateDiff(dd,0,GetDate()) % 7 = 0 > > Print 'It is Monday' > > Else > > Print 'It is not Monday' > > > > Tom > > > > "Steve Barker" <stevebarker@nospam.nospam> wrote in message > > news:DBD66596-30A8-4ED2-A4AE-92063558291C@microsoft.com... > > > I've got to the bottom of this now. When the OS on server 1 was installed, > > > it > > > was set to English U.S. In the U.S., the week starts on Monday (I think). > > > Server 2 was installed as England U.K. We use Sunday! Now, when SQL Server > > > is > > > installed, it defaults to the StartDay as given by the OS, so the SQL > > > Servers > > > were different from the off! > > > > > > So, of course, I should have used SET DATEFIRST to make sure everything > > > was > > > in-sync, but I didn't release it was even possible for the day codes to be > > > different! You live in learn... > > > > > > However, this all smacks of "magic number" programming to me, and now that > > > I'm aware of this little problem, I'm always going to use DATENAME(dw, > > > GETDATE()), which returns 'Monday' on a Monday. Since this is an in-house > > > system, I KNOW that it will never be used in France, for example, so I'll > > > never have to worry about "mardi", although in truth, that's how I'm > > > feeling > > > right now! > > > > > > "Hari Prasad" wrote: > > > > > >> Hi > > >> > > >> You could use DATEFIRST. See the below example. > > >> > > >> > > >> SET DATEFIRST 7 -- Sunday (This is default for US) > > >> go > > >> select datepart(week,GETDATE()) > > >> > > >> Thanks > > >> Hari > > >> SQL Server MVP > > >> > > >> > > >> "Steve Barker" <stevebarker@nospam.nospam> wrote in message > > >> news:D5056037-F24F-419A-A748-3248EC7AD882@microsoft.com... > > >> > Hi guys, > > >> > > > >> > I've noticed that the following line of T-SQL: > > >> > > > >> > SELECT DATEPART(dw, GETDATE()) > > >> > > > >> > ...when fired on different servers, gives different answers. For > > >> > instance, > > >> > as I write this, it's Monday. One server returned 1, and the other 2! > > >> > The > > >> > difference occurred when the T-SQL was run through Query Analyser, and > > >> > as > > >> > part of a stored procedure. > > >> > > > >> > Why is this?! Does each SQL Server have a setting where the first day > > >> > of > > >> > the > > >> > week is set? > > >> > > > >> > Thanks, > > >> > > > >> > Steve. > > >> > > > >> > > > >> > > >> > > >> > > > > > > a minor change should take care of it:
DateDiff(dd,'19000101',GetDate()) Now that would work, and is slightly more explicit than using "0" to
represent the beginning of SQL Server time. And, I suppose a simple comment above the line of code could explain what it is doing just in case the next person doesn't get it right away. Show quote "Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message news:1157485808.804162.155630@b28g2000cwb.googlegroups.com... > a minor change should take care of it: > > DateDiff(dd,'19000101',GetDate()) > On Mon, 4 Sep 2006 12:12:28 -0400, Tom Cooper wrote:
>One way that will work in the US, UK, and France is to take advantage of the Hi Tom (and Steve),>fact that January 1, 1900 was a Monday, and is also day 0 in SQL Server >datetimes. So you can use the number of days since then modulo 7 to >determine the day of the week, e.g., > >If DateDiff(dd,0,GetDate()) % 7 = 0 > Print 'It is Monday' >Else > Print 'It is not Monday' > >Tom Here's another way to prevent problems from an unexpected SET DATEFIRST: SELECT (DATEPART(dw, CURRENT_TIMESTAMP) + @@DATEFIRST) % 7 This will always return 0 for saturdays, 1 for sundays, ..., and 6 for fridays. -- Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics