CODEWITHSUNDEEP
sql-serversql-server-2012
Jeremy
Jeremy

Reputation: 121

Get date field based on Month and Year

I've got two fields, Period and YR with period representing the month. I would like to be able to select these records as a date field with the assumption that the day is the last day of the month. For example:

YR    Period      Date
2017   1          2017-01-31
2017   2          2017-02-28
Etc

I'm at a loss on how to go about doing this. Thanks for any help you can provide.

Upvotes: 1

Views: 97

Answers (3)

Nolan Shang
Nolan Shang

Reputation: 2328

Here is another query:

WITH tb(YR,Period)AS(

    SELECT 2017,1 UNION ALL
    SELECT 2017,2 UNION ALL
    SELECT 2017,3 
)
SELECT DATEADD(DAY,-1,DATEADD(MONTH,tb.Period+1, DATEADD( YEAR, YR-1900,0))) FROM tb


-----------------------
2017-02-28 00:00:00.000
2017-03-31 00:00:00.000
2017-04-30 00:00:00.000

Upvotes: 0

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

This is how you could do it using "basic" functions:

; with data as (
     select 2017 as yr, 1 as period 
     union all 
     select 2017 as yr, 2 as period
    )
    , temp as (
    select 
      yr, 
      period, 
      dateadd(day, -1, dateadd(month, 1, 
                convert(datetime, cast(yr*10000 + period*100 + 1 as varchar))
                       )
     ) as test
    from data
    )
    select yr, period, convert(varchar(10), test, 126) from temp

Upvotes: 1

DVT
DVT

Reputation: 3127

SELECT YR, Period, EOMONTH(DATEFROMPARTS(YR, Period, 1)) AS [Date]
FROM <<table>>

Upvotes: 5

Related Questions