Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

last friday in impala

avatar
Explorer

Hi, 

how do i get last Friday in  Impala?

 

Thx

1 ACCEPTED SOLUTION

avatar
Super 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.

View solution in original post

2 REPLIES 2

avatar
Super 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.

avatar
Explorer

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

Thats  what i needed. Thank you!!