Home Forums SQL Server 2008 T-SQL (SS2K8) get the first date and weekday of every month in a year RE: get the first date and weekday of every month in a year

  • The best option could be a calendar table that can establish those values without further calculations.

    If you don't have it, or can't create it, here's a possible option. Be sure to understand it and ask any questions you have before implementing it. It uses a Tally (or numbers) table in the form of a CTE. Understanding this concepts might help a lot (for this problem and many others).

    DECLARE @Yearchar(4) = '2014';

    WITH E1 AS(

    SELECT N FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E(N)

    ),

    E3 AS(

    SELECT a.N FROM E1 a, E1 b, E1 c

    ),

    cteTally AS(

    SELECT TOP (360) DATEADD( dd, (ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1), CAST(@Year AS date)) myDate

    FROM E3

    ),

    weekDays AS(

    SELECT myDate, ROW_NUMBER() OVER(PARTITION BY MONTH(myDate) ORDER BY myDate) rn

    FROM cteTally

    WHERE DATENAME( WEEKDAY, myDate) NOT IN ('Saturday', 'Sunday')

    )

    SELECT myDate FirstWeekDay,

    DATEADD(MM, DATEDIFF(MM, 0, myDate), 0) FirstDay

    FROM weekDays

    WHERE rn = 1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2