What is the Impala SQL equivalent function of NEXTDAY in Netezza?

Question

I have a SELECT statement that I am trying to convert from Netezza SQL to Impala SQL. The output looks something like 140612, which is a date that is obtained by subtracting 7 from the current date and then pulling out the monday of that week.

I need to have this readable for Impala, then format it, then turn it into a string.

The query is :

TO_CHAR(next_day(DATE(a.date)-7, 'Monday'), 'YYMMDD') AS START_DATE

Answer 1

Assuming a.date is a timestamp, and T is the day of the week (1 = Sunday, 7 = Saturday; for your example above, Monday = 2, so T = 2) you should be able to use use

date_add(a.date, 7 - pmod(dayofweek(a.date) - T, 7));

in place of next_day in the above query. Check out the documentation on Impala's built-in date and time functions for more detail.