Extract last day of the month from MMM(M)YYYY

Question

How can I extract the last day of the month from dates like

DEC2009, APR2013, AUG2008, NOV2017, JUL2014 etc.

I’ve tried datepart, convert, cast, month, year and I keep getting in a mess with type conversion errors.

Answer

Use try_convert and eomonth()

declare @date varchar(7)='jul2014'

select Day(EOMonth(Try_Convert(date, @date)))

Accepted Answer

Use try_convert and eomonth()declare @date varchar(7)='jul2014'select Day(EOMonth(Try_Convert(date, @date)))