INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

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

Posting Guidelines

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

Jobs

Date and time Tips and tricks

How many days from a given date to the first given day of week by tektipdjango
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 Archive

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close