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 1

Use try_convert and eomonth()

declare @date varchar(7)='jul2014'

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

Answer 2

You may try the following:

SELECT
    dt,
    DATEADD(day, -1, DATEADD(month, 1,
                         CONVERT(datetime, '01' + dt, 106))) AS dt_eom
FROM yourTable;

Demo

The strategy here is to first build from e.g. DEC2009 the string 01DEC2009. Then, we use CONVERT with mask 106. We obtain the final day of the month by adding one month and then subtracting one day.

Answer 3

You can use this

DECLARE @date VARCHAR(20)='DEC2009'

SELECT Day(CONVERT(NVARCHAR,CAST(DATEADD(MM,DATEDIFF(MM, -1, @date),-1) AS 
DATE),100))

And

SELECT
date,
Day(CONVERT(NVARCHAR,CAST(DATEADD(MM,DATEDIFF(MM, -1, date),-1) AS 
DATE),100)) AS date_eom
FROM Table;

Answer 4

You can use the EOMONTH function to get the last day in a month.

First construct an actual date value: add 01 to the beginning, then convert using style 106. If you just use CONVERT or CAST without a style parameter, you may run into issues due to local culture. Instead you should specify the style explicitly.

SELECT
    dt,
    EOMONTH( CONVERT(date, '01' + dt, 106) ) AS dt_eom
FROM yourTable;

db<>fiddle

Answer 5

Thanks all (except for Larnu who didn't seem to understand or answer the question)

Your helpful advice and specifically the "TRY_CONVERT" allowed me to work that the two entries where the months were 4 characters fields that were throwing it. My final code was:

EOMONTH(CONVERT(DATE, LEFT(TRIM(myField),3) + RIGHT(TRIM(myField),4)))

and it worked fine. :)

Happy days!