- 3 Posts
- 734 Views
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:
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. :-)