asked on

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

ASKER CERTIFIED SOLUTION

DonKronos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

louise001

ASKER

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?

DonKronos

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.

louise001

ASKER

That's helpful, thanks, will award points

Louise