Convert all dates to last day of year

apatten

I am trying to convert all my dates to the last day of the year in a beast mode. I have a column in my dataset that is already showing the year for the data and have tried this

STR_TO_DATE( CONCAT(`YEAR`,'-',12,'-',31),' %Y-%m-%d ')

It doesn't seem to work. Any help would be appreciated

ST_Superman

Not sure if it was a typo or not... but you have some commas in strange places. Try this:

STR_TO_DATE(CONCAT(`YEAR`,'-12-31'),'%Y-%m-%d')

MichelleH

@apatten You also had spaces extra spaces in your date format qualifiers, which would prevent it from recognizing the dates. @ST_-Superman-_ 's formula should fix that.

GrantSmith

For another option:

STR_TO_DATE(`y` + 1, '%Y') - 1

'%Y' - defaults to the first of the year

+1 Add a year

-1 Subtracts a day

y here is the year in integer format.

In other words, it's converting the given year to be the first day of the next year then subtracting 1 day to get the last day of the given year