get the first Sunday and last Saturday

  • hI,

    can anyone help me how to get the first Sunday and last Saturday of every month for a year.

    i need to fetch the dates for the the first Sunday and last Saturday of every month and insert into a column.

    please do the needful

  • here's the code to get the first sunday of this month;

    the last sunday of the previous month is the same date minus 7 days.

    you could change the code to get the first Saturday of a given month,a dn then subtract 7 days;

    --first sunday of this month....

    -1=sunday,-2=saturday,-3-friday etc

    SELECT

    datename(dw,dateadd(dd,-1,DATEADD(wk,

    DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0))),

    dateadd(dd,-1,DATEADD(wk,

    DATEDIFF(wk,0,dateadd(dd,7-datepart(day,getdate()),getdate())), 0))

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Look at this, and with slight modification you should be able to do what you require

    https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • This should fo the trick for you:

    -- Set year in a variable

    DECLARE @Year int

    SET @Year = 2010

    ;WITH Months AS (

    -- Create a month numbers CTE

    SELECT 1 AS MonthNumber

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    UNION ALL SELECT 6

    UNION ALL SELECT 7

    UNION ALL SELECT 8

    UNION ALL SELECT 9

    UNION ALL SELECT 10

    UNION ALL SELECT 11

    UNION ALL SELECT 12

    UNION ALL SELECT 13

    ),

    Dates AS (

    -- Find first day of month

    SELECT monthNumber,

    firstDayOfMonth = DATEADD(month, monthNumber - 1, CONVERT(datetime, CAST(@Year as char(4)) + '0101', 112))

    FROM Months

    ),

    MonthRange AS (

    -- Find last day of month

    SELECT *, lastDayOfMonth = (

    SELECT TOP 1

    DATEADD(day, -1, firstDayOfMonth)

    FROM Dates

    WHERE MonthNumber = D.MonthNumber + 1

    )

    FROM Dates AS D

    WHERE monthNumber <= 12

    )

    SELECT *, firstSunday = (

    SELECT TOP 1

    DATEADD(day, monthNumber -1, firstDayOfMonth)

    FROM Months

    WHERE DATEPART(weekday, DATEADD(day, monthNumber -1, firstDayOfMonth)) = 1

    ORDER BY monthNumber

    ),

    lastSaturday = (

    SELECT TOP 1

    DATEADD(day, (-1) * (monthNumber -1), lastDayOfMonth)

    FROM Months

    WHERE DATEPART(weekday, DATEADD(day, (-1) * (monthNumber -1), lastDayOfMonth)) = 7

    ORDER BY monthNumber

    )

    FROM MonthRange

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • Thank you Gianluca, you code fulfilled my requirement.

    i need to knon one more thing, i replaced monthNumber <= 24 for getting two years data but it didn't work can you help me in getting the same.

  • With a few changes you can have up to 12 years.

    With a real tally table[/url] you could have all the years you want.

    -- Set year in a variable

    DECLARE @StartYear int

    DECLARE @NumYears int

    SELECT @StartYear = 2010, @NumYears = 2

    ;WITH SmallTally AS (

    -- Create a month numbers CTE

    SELECT 1 AS N

    UNION ALL SELECT 2

    UNION ALL SELECT 3

    UNION ALL SELECT 4

    UNION ALL SELECT 5

    UNION ALL SELECT 6

    UNION ALL SELECT 7

    UNION ALL SELECT 8

    UNION ALL SELECT 9

    UNION ALL SELECT 10

    UNION ALL SELECT 11

    UNION ALL SELECT 12

    UNION ALL SELECT 13

    ),

    Months AS (

    SELECT N AS MonthNumber

    FROM SmallTally

    ),

    Years AS (

    SELECT @StartYear + N - 1 AS YearNumber

    FROM SmallTally

    WHERE N <= @NumYears

    ),

    Dates AS (

    -- Find first day of month

    SELECT monthNumber, YearNumber,

    firstDayOfMonth = DATEADD(month, monthNumber - 1, CONVERT(datetime, CAST(YearNumber as char(4)) + '0101', 112))

    FROM Months, Years

    ),

    MonthRange AS (

    -- Find last day of month

    SELECT *, lastDayOfMonth = (

    SELECT DATEADD(day, -1, firstDayOfMonth)

    FROM Dates

    WHERE monthNumber = D.MonthNumber + 1

    AND YearNumber = YEAR(D.firstDayOfMonth)

    )

    FROM Dates AS D

    WHERE monthNumber <= 12

    )

    SELECT *, firstSunday = (

    SELECT TOP 1

    DATEADD(day, N -1, firstDayOfMonth)

    FROM SmallTally

    WHERE DATEPART(weekday, DATEADD(day, N -1, firstDayOfMonth)) = 1

    ORDER BY N

    ),

    lastSaturday = (

    SELECT TOP 1

    DATEADD(day, (-1) * (N -1), lastDayOfMonth)

    FROM SmallTally

    WHERE DATEPART(weekday, DATEADD(day, (-1) * (N -1), lastDayOfMonth)) = 7

    ORDER BY N

    )

    FROM MonthRange

    ORDER BY firstDayOfMonth

    -- Gianluca Sartori

  • Thank you Gianluca, i really appreciate you.

  • Gianluca's solution is missing one minor, easily overlook thing. Easily fixed with four lines of code:

    -- at the top of the code

    DECLARE @OldDateFirst tinyint;

    SET @OldDateFirst = @@DateFirst;

    SET DATEFIRST 7;

    -- put at the end of the code to restore:

    SET DATEFIRST @OldDateFirst;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Nice catch, Wayne.

    An alternative way could be checking the results of DATEPART(weekday) for known dates and replace the constants 1 and 7 with the variables.

    DECLARE @Sunday int, @Saturday int

    SELECT @Sunday = DATEPART(weekday, '20101031'), @Saturday = DATEPART(weekday, '20101030')

    -- Gianluca Sartori

  • Keep it simple and avoid any reference to SET DATEFIRST!

    DECLARE@Year SMALLINT = 2011

    ;WITH cteCalendar(FirstOfMonth, LastOfMonth)

    AS (

    SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,

    DATEADD(MONTH, 12 * @Year + number - 22800, -1) AS LastOfMonth

    FROMmaster..spt_values

    WHERETYPE = 'P'

    AND number BETWEEN 1 AND 12

    )

    SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,

    DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday

    FROMcteCalendar


    N 56°04'39.16"
    E 12°55'05.25"

  • Nice one, Peso!

    Well, I wouldn't say "simple", I would say "short" instead! 🙂

    Very nicely done, as usual.

    -- Gianluca Sartori

  • SwePeso (10/27/2010)


    Keep it simple and avoid any reference to SET DATEFIRST!

    DECLARE@Year SMALLINT = 2011

    ;WITH cteCalendar(FirstOfMonth, LastOfMonth)

    AS (

    SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,

    DATEADD(MONTH, 12 * @Year + number - 22800, -1) AS LastOfMonth

    FROMmaster..spt_values

    WHERETYPE = 'P'

    AND number BETWEEN 1 AND 12

    )

    SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,

    DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday

    FROMcteCalendar

    Peso, I agree. Very short and sweet, with a beautiful execution plan to boot! The select from the CTE is doing a beautiful calculation to determine the appropriate day of the week.

    As a result of dissecting this (in order to understand it) I have a few questions and observations.

    1. SELECTDATEADD(MONTH, 12 * @Year + number - 22801, 6) AS FirstOfMonth,

    This is actually returning everything as the 7th of the month. The 6 needs to be changed to 0 to make it return the 1st.

    This means that the first 6 needs to be changed to a 0 in this line also:

    SELECTDATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,

    2. (In the first bit of code I quoted above) I assume that the reason you're using MONTH is because that is the shortest interval with a consistent # over the course of a year. Days may have 365 or 366 in a year; Weeks may have 52 or 53 in a year.

    3. (In the same first bit of quoted code above) I completely do not understand what the numerical calculation is doing. 12*2011+number(1-12)-22801 returns the first of the month for each month of the specified year. 22801 corresponds to a date of Feb 01, 3800. So, where does 22801 come from? How did you come up with this?

    Like I said, I'm just trying to understand what is going on here.

    FWIW, I modified your code into something that makes more sense to me. However, it adds an additional Compute Scalar to the execution plan, and I don't see why. I also added in the OPs request for being able to specify the # of years.

    DECLARE @Year SMALLINT,

    @Date datetime,

    @NbrYears tinyint;

    SET @Year = 2010;

    SET @Date = CONVERT(char(4),@Year)+'0101';

    SET @NbrYears = 3;

    ;WITH cteCalendar(FirstOfMonth, LastOfMonth)

    AS (

    SELECT DATEADD(MONTH, number-1, @Date) AS FirstOfMonth,

    DATEADD(MONTH, number, @Date)-1 AS LastOfMonth

    FROM master..spt_values

    WHERE TYPE = 'P'

    AND number BETWEEN 1 AND (12 * @NbrYears)

    )

    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,

    DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday,

    FirstOfMonth,

    LastOfMonth

    FROM cteCalendar

    Thanks,

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 22800 = 1900 * 12

    so

    DATEADD(MONTH, 12 * @Year + number - 22801, 6)

    is equivalent to

    DATEADD(MONTH, 12 * (@Year - 1900) + number - 1, 6)

    but saves 1 addition operation.

  • WayneS (10/27/2010)


    This is actually returning everything as the 7th of the month. The 6 needs to be changed to 0 to make it return the 1st.

    If you do, you can end up with a date from previous month.

    This can happen due to the integer division by 7.

    So, if I instead start with the seventh day of the month, I am guarateed that the first sunday is within these seven days, even after the integer division. I am doing the same with the LastOfMonth.

    FirstOfMonth doesn't imply the first day... 😉


    N 56°04'39.16"
    E 12°55'05.25"

  • andrewd.smith (10/27/2010)


    ...but saves 1 addition operation.

    Doesn't save a lot of headscratching 😉

    Peso's code is incredibly smart. Full stop.

    If there's one minor thing that can be criticized, personally, I prefer code that can easilly be understood and maintained. When I add something "obscure" to my code, I tend to comment it. Probably because I'm a bit thick-headed, but also because people with a thicker head than mine could have to change it some day.

    His code is much more efficient than the one I posted because the execution plan is far less demanding: it doesn't need to save 1 microsecond avoiding 1 addition operation. The microsecond you save today could turn into hours lost next year trying to understand what the code does. The fact itself that an experienced developer like Wayne got in trouble with it should be sufficient to make the point.

    -- Gianluca Sartori

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply