how to get nth date of each month for an year

Question

How to get the nth date of each month if "sunday" then that next Immediate date n+1 for a year. for example :

 Year=2014
 nth value = 2(2nd of each month)

 Date:
 2014-01-02
 2014-02-03 (since 02 is "Sunday" next Immediate date)
 2014-03-03 (since 02 is "Sunday" next Immediate date)
 2014-04-02
 2014-05-02
 2014-06-02
 2014-07-02 
 and so on...

read this answer and have a go yourself, http://stackoverflow.com/a/1111013/659190 — Jodrell, May 22 '14 at 09:36
How do you wish to handle values towards the end of the month (e.g. 28 if February ends on a Sunday, + all values > 28 will be edge cases)? — Damien_The_Unbeliever, May 22 '14 at 09:37
what if the day is the last of the month, and its a Sunday, should you roll over to the first? — Jodrell, May 22 '14 at 09:39
@Jodrell what if the day is the last of the month, and its a Sunday, should you roll over to the first? No,instead it should set the next date i.e 1st of next month. — ViZ, May 22 '14 at 09:45
So, is the 31st of February usually the 3rd of March (2nd when a leap year and the 2nd isn't a Sunday, 4th if the 3rd is a Sunday)? — Damien_The_Unbeliever, May 22 '14 at 10:15
@Jodrell - if we get to that point (we're still just dealing with EOM and Sunday issues) then it'd be time to suggest a calendar table. — Damien_The_Unbeliever, May 22 '14 at 10:29

Jodrell · Accepted Answer · 2014-05-22T10:23:00.593

1

how about this, fiddle here

Will work with day values between '01' and '28', I've left parameter checking and conversion to the OP.

DECLARE @Year = Char(4);
DECLARE @Day = VarChar(2);

SET @Year = '2014';
SET @Day = '2';

SELECT
    CASE DATENAME(dw, [Date])
        WHEN 'Sunday' THEN DATEADD(d, 1, [Date])
        ELSE [Date]
    END [Date]
  FROM (
    SELECT
        CAST(@Year + '-' + [M] + '-' + @Day AS DATETIME) [Date]
      FROM (
          SELECT '1' [M] 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') [Months]) [RawDays];

edited May 22 '14 at 10:23

answered May 22 '14 at 10:05

Jodrell

34,946
5
87
124

script should run on sql server 2005 – ViZ May 22 '14 at 10:15
@user1069940 fixed it now. If it doesn't work, tell me the error. – Jodrell May 22 '14 at 10:17
pls check where "2014-02-02" is "sunday" but still showing instead for "2014-02-03" – ViZ May 22 '14 at 10:20
changing to : DATEPART(dw, [Date]) to datename(dw,Date) WHEN 'sunday' is working fine now.. – ViZ May 22 '14 at 10:23
@user1069940, I've changed to use `DATENAME` this avoids confusion about when the first day of the week is. – Jodrell May 22 '14 at 10:25
@user1069940, you'll have to define what 'work' means for edge cases in your question. – Jodrell May 22 '14 at 11:15

score 0 · Answer 2 · answered May 22 '14 at 10:56

This worked for me...!

    Declare @Tracker int
    set @Tracker = 2

    Declare @FromDate datetime
    Declare @ToDate datetime
    set @FromDate = '2014-01-01'
    set @ToDate = '2014-12-31'

    Declare @TrackerTable Table ( Date Datetime)

    ;with DateDifference As
    (
         SELECT @FromDate DateValue
         UNION ALL
         SELECT  DateValue + 1
         FROM    DateDifference   
         WHERE   DateValue + 1 < @ToDate
    )

    Insert into @TrackerTable
        Select 
            Case 
                when datename(dw,DateValue) = 'Sunday' Then DateValue + 1 
                Else DateValue 
                End as Date
            from DateDifference where Day(DateValue) = @Tracker
            OPTION (MAXRECURSION 0) 

    Select * from @TrackerTable

how to get nth date of each month for an year

2 Answers2