• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 620
  • Last Modified:

Problem with weekday in datepart

Hi Experts,

In SQL Server 2000 I have the following query:

CREATE PROCEDURE dbo.xTestDatePart
AS SELECT     DATEPART(month, GETDATE()) AS 'Month Number', DATEPART(weekday, GETDATE()) AS Day
GO

Today is Tuesday. When I run this in QA on my server I get the results:
------------ -----------
7            2
@RETURN_VALUE = 0

and in my Access 2003 adp file running on my local machine connecting to the same db I get the results 7 3

On my server SELECT @@Datefirst returns 1 and in my adp file it returns 7

If anyone can advise where things are going wrong / what settings to examine in order to solve this problem I'd be grateful.

Thanks,

Louise
0
louise001
Asked:
louise001
  • 2
  • 2
1 Solution
 
DonKronosCommented:
Change the procedure like this to get 7 3 returned by sql server

CREATE PROCEDURE dbo.xTestDatePart
AS
SET DATEFIRST 7
SELECT     DATEPART(month, GETDATE()) AS 'Month Number', DATEPART(weekday, GETDATE()) AS Day
GO

0
 
louise001Author Commented:
That works so thanks for that. I don't understand though why Access and SQL server return different answers when they're looking at the same db (local settings in each application?) - could you clarify this?
0
 
DonKronosCommented:
You might want to look at this link.

http://msdn2.microsoft.com/en-US/library/ms187766.aspx

By default for U.S. English @@DATEFIRST is 7.  But it can be different for different languages, for example, for Italian @@DATEFIRST defaults to 1.

Also, @@DATEFIRST will reflect the most recent SET DATEFIRST in the session.  Thus, if you run a stored procedure that sets DATEFIRST prior to running your stored procedure you will get the value set by the previous stored procedure.

Also, the value of DATEFIRST is dependent on the settings withing the "language" (T-SQL or Access) and not the database itself.

Hope this helps.
0
 
louise001Author Commented:
That's helpful, thanks, will award points

Louise
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now