last friday in impala

[#1]

Hi, 

how do i get last Friday in  Impala?

Thx

[#2 · Accepted solution]

I am not sure if there are easier ways, but something like below might work:

SELECT CASE DAYOFWEEK(NOW())
WHEN 1 THEN DAYS_SUB(NOW(), 2) -- Sunday, so Friday was 2 days ago
WHEN 2 THEN DAYS_SUB(NOW(), 3) -- Monday
WHEN 3 THEN DAYS_SUB(NOW(), 4) -- Tuesday
WHEN 4 THEN DAYS_SUB(NOW(), 5) -- Wednesday
WHEN 5 THEN DAYS_SUB(NOW(), 6) -- Thursday
WHEN 6 THEN DAYS_SUB(NOW(), 7) -- Friday
WHEN 7 THEN DAYS_SUB(NOW(), 1) -- Saturday, so Friday just the day before
END AS last_friday;

If you just want the Friday from last week, regardless of which day you are on, then below might be easier:

SELECT DAYS_SUB(NOW(), DAYOFWEEK(NOW()) +1) AS last_friday;

Hope above helps.

[#3]

SELECT DAYS_SUB(NOW(), DAYOFWEEK(NOW()) +1) AS last_friday;

Thats  what i needed. Thank you!!