INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- Turn Off Ad Banners
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Member Feedback
"...I have learned more through this forum than I did on a two day course. Thanks to everyone for their help and other postings that I have found useful..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft SQL Server: Programming FAQ
|
Date and time Tips and tricks
|
How many days from a given date to the first given day of week
Posted: 25 Apr 04 (Edited 25 Apr 04)
|
The date of week calculation is not very easy.
here is a sample to calculate the first given day of week following (or equal to) a given date.
This is needed for instance when you want the first Tuesday in a month.
You have the following infos shown as variables, in the sample:
declare @givenDayOfWeek as int /* 1 for monday, 2 for tuesday...7 for sunday*/
declare @givenDate as datetime
declare @numberDaysFromGivenDateToFirstGivenDayOfWeek as integer
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7
Sample1 : find the date of the first Tuesday in May of year 2004
1-number of days from 1st of May 2004 to the first Tuesday
set @givenDate = '05/01/2004'
set @givenDayOfWeek = 2 /* 2 for Tuesday */
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7 /* the formula gives 3 */
2-Searched date of the first Tuesday in May 2004
declare @searchedDate as datetime
@searchedDate = @givenDate + @numberDaysFromGivenDateToFirstGivenDayOfWeek /* it gives 05/04/2004 which is the first tuesday in May 2004 */
Sample2 : find the date of the Last Friday in May of year 2004
1-Determine the reference date
The last Friday is within the 7 last days of May, therefore it is the first friday following(or equal) to the 25th of May 2004 (31 - 6) This is the trick !!
2-number of days from 25th of May 2004 to the first Friday
set @givenDate = '05/25/2004'
set @givenDayOfWeek = 5 /* 5 for Friday */
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7 /* the formula gives 3 */
3-Searched date of the last Friday in May 2004
declare @searchedDate as datetime
@searchedDate = @givenDate + @numberDaysFromGivenDateToFirstGivenDayOfWeek /* it gives 05/28/2004 which is the last friday in May 2004 */
Additionnal trick
If in your query you have a lot of records to calculate, you can simplify the formula by setting the @@DATEFIRST parameter
You just have to reset the param when you've finished to prevent miscalculation elsewhere.
declare @originalDATEFIRST as int
set @originalDATEFIRST = @@DATEFIRST
set DATEFIRST @givenDayOfWeek /* that's the trick */
...
set @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate))%7 /*Simplified formula*/
...
set DATEFIRST @originalDATEFIRST /* reset to original value*/
...
|
Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum
My FAQ Archive
 Email This FAQ To A Friend |
|
 |
|
