Forums / Categories / PeopleSoft / Technology / Trying to create date range expression for sysdate-7 to sysdate with DateTime data field for Query Manager query.

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

     
Log in to reply

Looks like your connection to Quest Oracle Community was lost, please wait while we try to reconnect.