Combining year column and month column to date

Question

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.

Year and month are integers, use appropriate datatypes. And if you are handeling dates, use date datatype. Using the right datatype makes everything easier. I also don't really get your question, are you asking if you can convert '09/2016' to a datetime? No you can't, a date needs a day. If you want to default to a day, you can use DATEFROMPARTS and SUBSTRING. — HoneyBadger
I tried casting them both as int and created my variable as int as well. When I do that, it doesn't give any results. Example: declare @reportdate as int = '201609' select.... from.... where.... and cast(reportyear as int) + cast(reportmonth as int) = atreportdate (I can't use more than one "at" symbol so I literally put at) — Lisbon

John Cappelletti John Cappelletti · Accepted Answer · 2017-01-10T17:32:07

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

Combining year column and month column to date

1 Answers