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?

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

    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.

     

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