CODEWITHSUNDEEP
sqlsql-servert-sqldate
Lisbon
Lisbon

Reputation: 49

Combining year column and month column to date

In my database, I have a report_year column (char(4)) and a report_month column (varchar(2)). I am making an ssrs report that would use a stored procedure and would pull data from this table and my parameters are the date and year. I am succesful at doing this by casting both of the columns and concatenating them, also adding a "/" in between. So in SSRS report, the parameter that users need to put is the month and date (ex. 09/2016).

Users want a drop down to get the dates. Since my parameter is a varchar, it would ask literally for the month and the year formatted above. Is there anyway to cast this to date without the day itself, only just the month and the year? I tried datediff and dateadd functions but I am not having any luck.

Upvotes: 0

Views: 906

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

Select BegMonth = cast(Replace('09/2016','/','/01/') as date)
      ,EndMonth = EOMonth(cast(Replace('09/2016','/','/01/') as date))

Returns

BegMonth    EndMonth
2016-09-01  2016-09-30

Upvotes: 1

Related Questions