Reply
New Contributor
Posts: 7
Registered: ‎01-09-2019
Accepted Solution

last friday in impala

Hi, 

how do i get last Friday in  Impala?

 

Thx

Cloudera Employee
Posts: 522
Registered: ‎03-23-2015

Re: last friday in impala

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.
Highlighted
New Contributor
Posts: 7
Registered: ‎01-09-2019

Re: last friday in impala

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

Thats  what i needed. Thank you!!

Announcements