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?
Advertisement
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.