Query Help (Date Related) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help (Date Related)

Author

Topic

CDBanks
Starting Member

23 Posts

Posted - 2005-05-13 : 11:08:24

If someone could help me with the following issue it would be greately appreciated. I am attempting to select records related to the previous week from a dB. The issue is our payroll week begins on Monday and SQL begins the week on Sunday by default. Is there a command or a setting I can use to declare the start of the week is Mondy?

The SQL I am using is abbreviated to:

SELECT dpvHstGndShift.DateOfBusiness
WHERE DATEPART(wk, dpvHstGndShift.DateOfBusiness) = DATEPART(wk, GETDATE()) - 1)

The result set is off by a day.

Thanks in advance for your consideration.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-13 : 11:23:03

from BOL

quote:

@@DATEFIRST
Returns the current value of the SET DATEFIRST parameter, which indicates the specified first day of each week: 1 for Monday, 2 for Wednesday, and so on through 7 for Sunday.

Syntax
@@DATEFIRST

Return Types
tinyint

Remarks
The U.S. English default is 7, Sunday.

Examples
This example sets the first day of the week to 5 (Friday), and assumes the current day to be Saturday. The SELECT statement returns the DATEFIRST value and the number of the current day of the week.

SET DATEFIRST 5
SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, GETDATE()) AS 'Today'

Here is the result set. Counting from Friday, today (Saturday) is day 2.

1st Day Today
---------------- --------------
5 2

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

CDBanks
Starting Member

23 Posts

Posted - 2005-05-13 : 11:32:16

Thanks for your help... As you may have guessed I am new at this and these 900 page manuals were just making me cross-eyed.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-05-13 : 17:11:09

books online is searchable, which allows for relatively easy lookups.

BOL is the SQL help file that comes with SQL client tools.

no problem though, you gotta start somewhere

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources