CODEWITHSUNDEEP
sqlsql-server-2008
franglais
franglais

Reputation: 938

Get month name with different month start and end dates

Getting the month for the current date is, obviously, straight forward, but I'm needing to get the month name with a different end date.

I need to get the month name with the start date of the month being the first Thursday after the first Wednesday of the month and the end date of the month being the first Wednesday of the following month. It's for an accounting thing, so I'm not going to argue with the spec!

e.g. for 2014, January would run from 9th Jan - 5th Feb, February would run from 6th February - 5th March, March would run from 6th March - 2nd April.

Upvotes: 1

Views: 336

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

I think, as per Paddy's answer, a lookup table is the simplest thing to do. Here's one way to generate the rows for it:

; With Numbers(n) as (
    select 4 union all select 5
), Months as (
    select CONVERT(date,'20010104') as StartDt,CONVERT(date,'20010207') as EndDt,
           DATENAME(month,'20010103') as Month
    union all
    select DATEADD(week,n1.n,StartDt),DATEADD(week,n2.n,EndDt),
           DATENAME(month,DATEADD(week,n1.n,StartDt))
    from Months,Numbers n1,Numbers n2 --Old-skool join, just for once
    where DATEPART(day,DATEADD(week,n1.n,StartDt)) between 2 and 8 and
          DATEPART(day,DATEADD(week,n2.n,EndDt)) between 1 and 7 and
          StartDt < '21000101'
)
select * from Months option (maxrecursion 0)

(CW since this is effectively just an extension to Paddy's answer but I don't want to edit their answer, nor is it suitable for a comment

Upvotes: 2

Paddy
Paddy

Reputation: 33857

I would suggest that you create a table with your 'accounting months' in it, having a start date, end date and month name columns.

You could then query this to find the row where your date is between the start and end dates and return the month name. Putting this into a scalar function would then allow it to be reusable and relatively easily updated for next years months as well.

Upvotes: 3

Related Questions