Convert all dates to last day of year

apatten
apatten
in Beast Mode

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

Tagged:

Comments

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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')

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • MichelleH
    MichelleH Coach

    @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
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories