Wednesday, January 27, 2010

First Monday of The Year

How to find the 1st Monday of the year for a given year ? This is a commonly ask question and there are lots of solution around.

Most of the solution uses a known reference date to do it or some based on the value return from datepart(weekday, <date>). The problem with datepart() is it is depending on the SET DATEFIRST value

Just try the following script and you will see

SET DATEFIRST 1
 
select  [datepart] = datepart(weekday, '2010-01-04'), 
        [datename] = datename(weekday, '2010-01-04')
 
SET DATEFIRST 2
 
select  [datepart] = datepart(weekday, '2010-01-04'), 
        [datename] = datename(weekday, '2010-01-04')


RESULT :
 
datepart    datename 
----------- ------------------------------ 
1           Monday
 
datepart    datename 
----------- ------------------------------ 
7           Monday

What I am showing here is a solution that is not dependant on the SET DATEFIRST and also uses any reference date to calculate the 1st Monday of the Year.


To find the 1st Monday of the year, you first determine the 1st day of the year and if it is not Monday, add the days required to it. For example, 1st day of year 2010 is on Friday. To get the 1st Monday of 2010, you need to add 3 days to it. The difficult part is the how to determine the number of days required.


The following scripts will illustrate this.









Declare


    @date    datetime,


    @year    int



 


select    @year    = 2010



 


--    1st of Jan of the year


select    @date    = dateadd(year, @year - 1900, 0)



 


--    1st monday of the year


select    @date     = dateadd(day, (@@datefirst - datepart(weekday, @date) + (8 - @@datefirst) * 2) % 7, @date)



 


select    [1st monday] = @date,


    datename(weekday, @date)



 


First, you determine the 1st day of the year. This is easy with dateadd(year, @year - 1900, 0).


Next is the find the number of days required. And here is the formula


(@@datefirst - datepart(weekday, @date) + (8 - @@datefirst) * 2) % 7



 



 

No comments:

Post a Comment