Convert String to Date/Time in Report Builder query in SQL

I have a column ENTRY_MONTH with dates in it as a string like 11/2017.

I’m trying to convert the column to datetime, preferably the last day of each month, so in the example above would be 11-30-2017.

I’ve tried

CONVERT(datetime, ENTRY_MONTH, 110)

to no avail. Any advice?

Answer

You can try something like:

DECLARE @MyDate varchar(16) = '11/2017'
SELECT DATEADD(d,-1,DATEADD(m,1,CONVERT(datetime, '1/' + @MyDate, 103)))

This uses a European format where the day comes first. Add a month on then take a day off takes you to the end of the month.