Support Questions

Find answers, ask questions, and share your expertise

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Cloudera Community
- :
- Support
- :
- Support Questions
- :
- Re: last friday in impala

Announcements

Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Labels:

New Contributor

Created 01-23-2019 02:00 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Hi,

how do i get last Friday in Impala?

Thx

1 ACCEPTED SOLUTION

Accepted Solutions

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

Guru

Created 01-24-2019 07:46 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

Guru

Created 01-24-2019 07:46 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Created 01-27-2019 08:01 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Thats what i needed. Thank you!!

Coming from Hortonworks? Activate your account here