Solved

why is sql 2005 treating Sunday as last day of week

Posted on 2008-10-22
16
670 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 143

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 143

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
 
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 143

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 143

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

734 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