×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# 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 by tektipdjango faq183-5074 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 datetimedeclare @numberDaysFromGivenDateToFirstGivenDayOfWeek as integerset @numberDaysFromGivenDateToFirstGivenDayOfWeek = (8-datepart(dw,@givenDate) + @givenDayOfWeek - @@DATEFIRST)%7Sample1 : find the date of the first Tuesday in May of year 20041-number of days from 1st of May 2004 to the first Tuesdayset @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 2004declare @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 20041-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 Fridayset @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 2004declare @searchedDate as datetime@searchedDate = @givenDate + @numberDaysFromGivenDateToFirstGivenDayOfWeek /* it gives 05/28/2004 which is the last friday in May 2004 */Additionnal trickIf in your query you have a lot of records to calculate, you can simplify the formula by setting the @@DATEFIRST parameterYou just have to reset the param when you've finished to prevent miscalculation elsewhere.declare @originalDATEFIRST as intset @originalDATEFIRST = @@DATEFIRSTset 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

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!