Create Month End Date column with Year & Month columns?

verytiredgirl · 2024-11-27T15:55:19+00:00

There was an error rendering this rich post.

My dataset has a Year & Month columns, I would like to add a 'Month End Date' Date column into this dataset with magic ETL. But each month has different end date so I'm not sure how to go on about this

Find more posts tagged with

Magic ETL

Accepted answers

ggenovese

There's a LAST_DAY() Function that gives the last day of the month for a given date:

LAST_DAY(STR_TO_DATE(CONCAT(`Year`,`Month`),'%Y%m'))

All comments

ArborRose

Can you use the year and month to build a date? And then use that date to calculate your end date? Say you create a formula to find the first of the month after and then subtract one day from that. I have to test this, but possibly something similar:

DATE_SUB(DATE_ADD(DATE(CONCAT(`Year`, '-', CASE WHEN LENGTH(`Month`) = 1 THEN CONCAT('0', `Month`) ELSE `Month` END, '-01')), INTERVAL 1 MONTH), INTERVAL 1 DAY)

ggenovese

There's a LAST_DAY() Function that gives the last day of the month for a given date:

LAST_DAY(STR_TO_DATE(CONCAT(`Year`,`Month`),'%Y%m'))

ArborRose

@ggenovese, Lol….I thought I was confusing computer languages. Obviously…that's a better route to use.