Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

last friday in impala

SOLVED Go to solution

last friday in impala

New Contributor

Hi, 

how do i get last Friday in  Impala?

 

Thx

1 ACCEPTED SOLUTION

Accepted Solutions

Re: last friday in impala

Guru
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.
2 REPLIES 2

Re: last friday in impala

Guru
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.

Re: last friday in impala

New Contributor

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

Thats  what i needed. Thank you!!