CODEWITHSUNDEEP
sqlsql-servert-sql
J. Davidson
J. Davidson

Reputation: 3307

Getting a date of each month in a year from a day

Hi I am trying to write a function so that it gets a date for example 10/31/2013. From this it take the day which is 31 and than starting with january it gives me list of 12 months date i.e

1/31/2013
2/30/2013
.....
...
12/31/2013

I can get the day part by using 

            declare @day integer 
            set @day=day(GetDate());

I could use a while look to create for each month. But I am wondering if there is better way of doing it. Please help

Upvotes: 1

Views: 149

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

declare @d date = '20130615'

select cast(dateadd(m, month, dateadd(year, datediff(year, 0, @d), -1)) as date) 
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) x(month)

EDIT after @Kaf's comment: Ok, I just assumed it was the last day, It works as I intended, but most likely not as mr J.Davidson intended. That just makes the script easier to write:

select cast(dateadd(m, month - month(@d), @d) as date) 
from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) x(month)

Upvotes: 2

Clockwork-Muse
Clockwork-Muse

Reputation: 13046

Personally, I tend to prefer recursive solutions too, although the simplicity of @t-clausen's answer is appealing. Note that you can't simply add a single month repeatedly, given the way the month lengths fluctuate (... and February regularly throws everybody off...). I think I might have tried something like this:

WITH Cycle_Dates (startOn, cycleStart, monthsElapsed)
                 as (SELECT startOn, startOn, 1
                     FROM Cycle
                     UNION ALL
                     SELECT startOn, DATEADD(month, monthsElapsed, startOn), monthsElapsed + 1
                     FROM Cycle_Dates
                     WHERE monthsElapsed < 13)
SELECT cycleStart
FROM Cycle_Dates

(I now consider it necessary for me to post SQL Fiddle examples where possible, to help keep me honest).

Upvotes: 0

Related Questions