CODEWITHSUNDEEP
sqlsql-server-2005
ViZ
ViZ

Reputation: 81

how to get nth date of each month for an year

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...

Upvotes: 0

Views: 1726

Answers (2)

ViZ
ViZ

Reputation: 81

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

Upvotes: 0

Jodrell
Jodrell

Reputation: 35696

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];

Upvotes: 1

Related Questions