First Monday of the Year in SQL Server

Question

How can I find "First Monday of the Year" using T-SQL ?

Solution 1

Here's the example from the link ngruson posted (http://sqlbump.blogspot.nl/2010/01/first-monday-of-year.html):

DECLARE @Date datetime
DECLARE @Year int = 2012

SET @Date = DATEADD(YEAR, @Year - 1900, 0)

SELECT DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, @Date) + 
    (8 - @@DATEFIRST) * 2) % 7, @Date)

The above returns:

2012-01-02 00:00:00.000

Solution 2

    Here, 
    1) DATEPART function for finding day of week. 
Here 1=sunday,2=monday,..,7=saturday.
    2) first find out is 2(monday) in case when
 condition then get same date,
    3) if is sunday(1) then add 1 day and get next
 day date is for monday,
    4) if is not 2(monday) and greater then 2 then find 
difference between lastday(7) and add 2 days ,
 so we reach to monday date.

    Here are you change year in @year variable. Ex:

        DECLARE @Date datetime
        DECLARE @Year int = 2012

        set @Date= convert(varchar(4),@Year) + '-01-01'
        select @Date,(case when DATEPART(DW,@Date)=2 then @Date
                     when DATEPART(DW,@Date)=1 then DATEADD(day,1,@Date)
                     else DATEADD(day,7-DATEPART(DW,@Date)+2,@Date)
                      end) as MondayDateis

Solution 3

Elegant it is not ;)

    DECLARE @year DATETIME = '01 jan 2012'

    SELECT [Day] 
    FROM
        (
            SELECT @year [Day] UNION ALL
            SELECT DATEADD(DAY, 1, @year) UNION ALL
            SELECT DATEADD(DAY, 2, @year) UNION ALL
            SELECT DATEADD(DAY, 3, @year) UNION ALL
            SELECT DATEADD(DAY, 4, @year) UNION ALL
            SELECT DATEADD(DAY, 5, @year) UNION ALL
            SELECT DATEADD(DAY, 6, @year)
        ) x
    WHERE
        DATENAME(DW, [Day]) = 'Monday'

Solution 4

Here's one example to accomplish that:

http://sqlbump.blogspot.nl/2010/01/first-monday-of-year.html

Solution 5

try this:

declare @yr int=2020
select case when datepart(weekday,dateadd(year,@yr-1900,0))=1 then dateadd(year,@yr-1900,1)
       else dateadd(dd,8-(datepart(weekday,dateadd(year,@yr-1900,0))),dateadd(year,@yr-1900,1))
end

SQL Fiddle demo

View more solutions

Comments

[Black Light]

...and if the language is not English ?

[Black Light]

Hello again. If you run your code for 2013, it says the first Monday is the 8th (which is a Tuesday). Its a small point, I know...

[satnhak]

Then there wouldn't be a first Monday of the year obviously

[Black Light]

Hi. I'll try and be more specific. Your suggested code does not work for all values of @@datefirst (see accepted answer for example). If your SQL Server environment has the first day of the week set as a Sunday (US default), you will get your answer. However, if you change the first day of the week to something else, like a Monday, you get a different answer.

[Jamie Marshall]

1900 - is not a 'magic' year. It is infact the year off of which most computing systems base their date structure.

[HABO]

@JamieMarshall It isn't clear from epoch that most computing systems treat 1900 as The Beginning Of Time. Would you be so kind as to provide a citation for this fact?

[Jamie Marshall]

@HABO - Great wiki link. I stand corrected.

[Jamie Marshall]

However the epoch date for SQL Server specifically is January 1 1900. So though I was technically wrong in my wording, I think the solution here is still needlessly complex, as that date will always work.

[HABO]

@JamieMarshall What defines the epoch for SQL Server? Yes, the base date for SQL Server's date/time data types is 1900-01-01. And 0001-01-01 and 1753-01-01. Then there is the two digit year cutoff configuration to enhance the fun of importing date strings. ...

[HABO]

@JamieMarshall ... Cast/Convert says "When casting character data representing only date or only time components to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01." 1900-01-01 was likely chosen since it is the earliest date supported by both data types.