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)
Maliki HassaniAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
can you confirm that if you have some dates like the below..

28-mar-2011
29-mar-2011
30-mar-2011
31-mar-2011
01-apr-2011
02-apr-2011
03-apr-2011
04-apr-2011 ( today which is monday ).

the query what i gave will get all this...

29-mar-2011
30-mar-2011
31-mar-2011
01-apr-2011
02-apr-2011
03-apr-2011
04-apr-2011

by the way, please can you post the output of the below query :

select to_char(UDF_CONVERT_UNIX_DATETIME(DMR.SCHEDULED_START_DATE_TIME, 'US/Eastern') ,'DD-MON-YYYY HH24:MI:SS') check_dates
from your_table/your_view
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)

Please let me know what output you need from them otherwise it is becoming to understand your question.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
could you please clarify? `
* do you want the 7 last mondays?
* or from last monday to this monday?
* or something else
0
 
Maliki HassaniAuthor Commented:
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.

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Maliki HassaniAuthor Commented:
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.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
an example of few sample records and what output need would be easy to understand.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
0
 
Maliki HassaniAuthor Commented:
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?
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
did you try what i gave you ?
0
 
Maliki HassaniAuthor Commented:
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
0
 
devindConnect With a Mentor Commented:
Following will give you range between yesterday and last Monday. Tweak it to suite you requirement

where UDF_CONVERT_UNIX_DATETIME(DATE_CREATED, 'US/Eastern')  in (
select sysdate  - rownum from dual connect by level <  decode( to_char(sysdate ,'D'),1,7, 2,8,to_char(sysdate,'D')-1 )  )

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.