asked on

Oracle: Syntax for replacing 7 day query to know all Mondays

Experts,

I have a query that pulls the date range from yesterday to 7 days back.
Code snippet:
WHERE UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY)
AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)

I would like to replace the 7 day so that it knows to pull all Mondays.

Any idea on how I can do this?

Thanks
(Oracle 10)

Guy Hengel [angelIII / a3]

could you please clarify? `
* do you want the 7 last mondays?
* or from last monday to this monday?
* or something else

Maliki Hassani

ASKER

Sorry,
When I say Monday, I mean that regardless of the day it is my query should pull data from yesterday and and exceed Monday's date.

Example:
If today I run my current query it goes back and pulls 7 days worth of data. Which goes past Monday and not what I am wanting. I would like for it to stop always on Monday, regardless of what day I run my query.

Maliki Hassani

ASKER

Correction to the top paragragh.
I mean that regardless of the day it is my query should pull data from yesterday and NOT exceed Monday's date.

Naveen Kumar

an example of few sample records and what output need would be easy to understand.

Naveen Kumar

try this. Not sure whether i got your question right .

WHERE UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY)
AND UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
and to_char((TRUNC(SYSDATE) - INTERVAL '7' DAY),'D') >= 2

Maliki Hassani

ASKER

Hi,

I am trying to do something like this
WHERE UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern') >= ((TRUNC(SYSDATE) - INTERVAL '7' DAY),'D') >= 2) <-- Fixed for Monday only
AND UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)

Any ideas?

Naveen Kumar

did you try what i gave you ?

Maliki Hassani

ASKER

Yes,

but no records are being displayed.

WHERE UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern') >= (TRUNC(SYSDATE) - INTERVAL '7' DAY)
AND UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
and to_char((TRUNC(SYSDATE) - INTERVAL '7' DAY),'D') >= 2

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

devind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial