why is sql 2005 treating Sunday as last day of week

In Microsoft Access this function will return 1.
   Weekday(#10/5/08#)
To me this is correct because 10/5/08 is a sunday which by computer standards is the first day of the week.

I can run the following line of code in a stored procedure on an SQL Server2000 database and it returns 1.
   Select DatePart(weekday, '10/5/08') as Test
Still correct. I run that same SP on my customer's SQL Server2005 database and it returns 7.
I modifed the SP so that it now reads:
  Set Datefirst 1 Select DatePart(weekday, '10/5/08') as Test
It still returns 7.

Is there some setting for SQL 2005 that I'm missing?  Is there a server setting creating this?
LVL 6
Mach1proAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
Set Datefirst 1 tells SQL Server to treat Monday as the start of the week, and thus Sunday = 7.

Try using SET DATEFIRST 7 to force Sunday to be considered #1.
0
 
Mach1proAuthor Commented:
I have done that for a fix in a critical portion of my code. The problem is our database was designed in SQL 2000 and we have some clients running 2000 while others run 2005.  Why would microsoft change this default? Is there a setting in 2005 to make it compatible with 2000 and treat Sunday as 1.
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
there is no difference between sql 2000 and sql 2005 in that matter.
what could differ is the language setting of the login you use.
if the language is set to us-english vs uk-english, you can get that difference for example also.
playing safe is to use the SET DATEFIRST code in any case.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mach1proAuthor Commented:
If I use the Set DateFirst code then the sp isn't portable between the two systems.  Would the language setting vary for each workstation or is that based on the server?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>If I use the Set DateFirst code then the sp isn't portable between the two systems.
why not?

>Would the language setting vary for each workstation or is that based on the server?
it's per login
0
 
Anthony PerkinsConnect With a Mentor Commented:
>>Why would microsoft change this default? Is there a setting in 2005 to make it compatible with 2000 and treat Sunday as 1.<<
It has not changed.  You can see this for yourself by doing:
Select @@DATEFIRST
On both boxes.  I suspect they are the same.

The point you may be missing is that it does not have to do with the first day of the week, but rather on the lousy way you are formatting the date in the DATEPART() function and the regional settings on that box.

The problem is that '10/5/08' is ambiguous.  Does it mean Oct 5, 2008 or May 10, 2008.  Yes, I realize that you mean the first, but guess what? your SQL Server 2005 box thinks it is the second.  Rather than use this type of date format use an unambiguous format such as this:
Select DATEPART(weekday, '20081005') as Test, DATEPART(weekday, '20080510') as Test2

Again don't take my word for it, try:
Select DATEPART(month, '10/5/08') as Test

And see what you get.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
actually, the best way to ensure correct date string conversion is to convert explicitly:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
1 = mm/dd/yy
DATEPART( weekday, CONVERT(datetime, '10/5/08', 1))
 
3 = dd/mm/yy
DATEPART( weekday, CONVERT(datetime, '10/5/08', 3))
 
11 = yy/mm/dd
DATEPART( weekday, CONVERT(datetime, '10/5/08', 11))

Open in new window

0
 
Patrick MatthewsCommented:
>>actually, the best way to ensure correct date string conversion is to convert explicitly:

That, and it is best to avoid m/d/yy and d/m/yy altogether, as those formats can be ambiguous.  Stick
with something like yyyy-mm-dd, which is never ambiguous.
0
 
Mach1proAuthor Commented:
I only presented 10/5/08 as an example to show what is happening between two different servers.  The coding for the SP actually is looking for the week number of the year which is based on what the first day of the week is. I was using Set Datefirst 1, which was fine until I found out that one system needs to have changed to Datefirst 7.  I'm not looking for a coding solution, but rather an explanation of why one system treats Sunday as the first day of the week and another system treats Sunday as the last day of the and what changes i need to make to that system (hopefully just sql server and the server itself or individual workstations) in order to get sql server 2005 system to calculate Sunday as the first day of the week.
0
 
Anthony PerkinsCommented:
>>rather an explanation of why one system treats Sunday as the first day of the week and another system treats Sunday as the last day <<
Yeah, I think we have beaten that horse to death.

Good luck.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree. please refer to my first comment: it's the login language setting...
0
 
Mach1proAuthor Commented:
Let me simplify my question further, because I still don't see it being answered.

1. Is there an option setting in SQL Server 2005 (not a code setting) so that SQL server will treat Sunday as the first day of the week?  If yes, where is the option?

2. If the answer to question 1 is NO, then is it sufficient to just change the regional settings on the computer hosting SQL Server 2005 to force Sunday as the first day of the week or do I have to change the regional settings on every workstation accessing Sql serrver 2005?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
1. it's a setting per login: the language of the login influences which is the "datefirst" value applied implicitly

2. the regional settings do NOT influcence the "datefirst"
0
 
dhoppeCommented:
If you database has the modulo-function (mod) try something like this:

YourWeekday = (((Weekday(YourDate)-1) + Weekday(#10/5/08#)/7) mod 7)+1

Whatever the setting of your environment is, you will get 1 for sunday, 2 for monday, and so on because

Weekday(#10/5/08#)/7

will return 1 or 0 depending on the actual setting,  if  #10/5/08# is a sunday.
0
 
Mach1proAuthor Commented:
Maybe that mod function can be used within SQL itself so I can get the correct starting date. Here is the problem I'm facing: in reality my problem is handling the datepart(week, ) function. The code I have creates a dynamic string for a stored procedure for company time log entries.  Originally, I set the datefirst property for the sp based on the company's starting day for the work week whether that be 1 for sunday, 2 for wednesday, etc.  The way I found the problem was when an employee worked on a Sunday and that particular workday hours fell into the wrong Week grouping.
So what it appears that I need to fix this is within my syntax is some way to determine how that sql server is treating the first day of the week and then adjust my DateFirst setting accordingly.
In other words, if the server is Showing Sunday as a 7 and my customer's workweek starts on a sunday, then I need datefirst set as 7 rather than 1, but if the server is treating Sunday as a 1, then I would set my Datefirst as 1.
I'm probably confusing some of you guys, because I'm confusing myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.