Solved

why is sql 2005 treating Sunday as last day of week

Posted on 2008-10-22
16
661 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:Mach1pro
  • 5
  • 5
  • 2
  • +2
16 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 168 total points
ID: 22777968
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
 
LVL 6

Author Comment

by:Mach1pro
ID: 22778168
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 168 total points
ID: 22780183
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
 
LVL 6

Author Comment

by:Mach1pro
ID: 22780240
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22780626
>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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 168 total points
ID: 22782636
>>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22783676
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22783926
>>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
 
LVL 6

Author Comment

by:Mach1pro
ID: 22814677
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22818152
>>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22819684
I agree. please refer to my first comment: it's the login language setting...
0
 
LVL 6

Author Comment

by:Mach1pro
ID: 22820318
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22820332
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
 

Expert Comment

by:dhoppe
ID: 23006839
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
 
LVL 6

Author Comment

by:Mach1pro
ID: 23009766
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

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now