Trying to create date range expression for sysdate-7 to sysdate with DateTime data field for Query Manager query.

Hi all you query heads.  I have a situation that I am struggling with.  I have dug as much as I can and have hit a wall.  Here's the situation:

I have created a query to show PO line data for dispatched POs.  Our user wants to schedule this report to run on a schedule and not have to enter any values.  This is the easy part.  The hard part is creating the expression to pull the time frame.  I can create the expression easily for Date fields but when it comes to DateTime fields, I cannot get it to work.

I can get this to work in the Query Manager no problem [ G.PO_DT BETWEEN TO_DATE(SYSDATE-7,'YYYY-MM-DD') AND TO_DATE(SYSDATE,'YYYY-MM-DD')  ]

I cannot get this to work [ DATETIME_DISP BETWEEN TO_DATE(SYSDATE-7,'YYYY-MM-DD') AND TO_DATE(SYSDATE,'YYYY-MM-DD') .  I have created an expression as Expression Type as Date and Expression Text as DATETIME_DISP.and am using this as the field.

Has anyone else built a query that uses a DATETIME field with an expression to pull the last 7 days on a recurring query?  If so, how'd you do it?

You could use to_timestamp , at least in pure SQL:

select CREATED_DTTM

from PS_FORM

where CREATED_DTTM between TO_TIMESTAMP(SYSDATE-7,'YYYY-MM-DD') AND TO_TIMESTAMP(SYSDATE,'YYYY-MM-DD');

PS_QUERY does odd stuff with dated and timestamps, often wrapping them like this:

TO_CHAR(CAST((B.CREATED_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF')

So you might need to do something similar to what you are comparing to.  I usually look at the SQL to see what is is comparing to and adjust my expressions accordingly.

Wow, just as I posted this in desparation I played around with it and actually figured it out.

I changed the expression to the following:

Expression Type = Date

Expression = to_char(to_date(to_char(DATETIME_DISP,'YYYY-MM-DD'),'YYYY-MM-DD'),'YYYY-MM-DD')

I was then able to use my expression of [ between SYSDATE-7 and SYSDATE ].

It works like a charm now.  :-)