The below Query helps you to get the First Day and Last Day of a month. This can also be used in TABLE_DATE_RANGE to retrieve data for Previous Months.
SQL
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,-1,"MONTH") First_Day_Previous_Month,
DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE()),"DAY") ,0,"MONTH") Last_Day_Previous_Month,
DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,0,"MONTH") First_Day_Current_Month
SQL
SELECT DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,-1,"MONTH") First_Day_Previous_Month,
DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE()),"DAY") ,0,"MONTH") Last_Day_Previous_Month,
DATE_ADD(DATE_ADD(CURRENT_DATE(),-DAY(CURRENT_DATE())+1,"DAY") ,0,"MONTH") First_Day_Current_Month
This works with the new syntax
ReplyDeleteDECLARE date_ref , period_start, period_end, first_day DATE;
SET date_ref = DATE '2020-3-30';
SET first_day = DATE_TRUNC(date_ref, MONTH);
SET period_start = DATE_SUB(first_day, INTERVAL 1 MONTH);
SET period_end = DATE_SUB(first_day, INTERVAL 1 DAY);
SELECT
period_start First_Day_Previous_Month,
period_end Last_Day_Previous_Month,
first_day as First_Day_Current_Month