Coding For Dates In SQR PDF Print E-mail
Written by Buck Vandiver   
Thursday, 01 April 2010 08:44

There are three ways to handle dates in SQR:

 

  • Use SQR’s date logic.  Variables should either be declared as Dates or read in by begin-select statements as dates.  SQR has delivered date functions, such as dateadd() and datediff().
  • Use PeopleSoft’s date procedures.  DATETIME.sqc and DATEMATH.sqc have several of these, such as Format-DateTime().  The DATEMATH.sqc has old procedures; the SQR’s date logic functions would be better to use.
  • Use the database’s date logic in the actual SQL statement Example: Select count(*) from ps_table where effdt > (sysdate – 7).

 

SQR’s Date Logic

A portion of the following is from http://peoplesoft.wikidot.com/dates-in-sqr

 

There are two main ways to store and use dates in SQR:

  • Store them as a character string in a string variable OR
  • Store them as date variables

Both types of variables (strings and dates) use the $ dollar sign prefix but dates are declared differently to strings.

Why would we use a date variable instead of a string? The main reason is to perform a date based calculation. If you only need to store a date in a variable, then using a string is fine. However, if you want to perform a calculation such as adding 7 days to a date, then a date variable should be used. Date variables are also required when using the delivered SQR date functions.

Declaring a date variable

Date variables are declared in the setup section like this:

begin-setup

    declare-variable

        date $dtVariable

        !date is the reserved variable on the prior line.

    end-declare

end-setup

 

Note that date variables are initialized to null when they are declared.

SQR Date Functions

(These are from SQR, not PeopleSoft.  PeopleSoft has their own functions that work off the date being an actual string variable.)

These functions are on page 59 and 575 in the book SQR in PeopleSoft.

Now that you have a date variable, there is a number of delivered date functions you can use in SQR including:

  • strtodate() to convert a string to date
  • datetostr() to convert a date to a string
  • dateadd() to add to a date
  • datediff() to subtract dates
  • datenow() to get the current date

The following are examples of how to use dateadd() and datediff(), probably the two most frequently used functions:

Example 1: using datediff() 

Return the number of days between the column variable, &EFFDT and the variable $BaseDate:

let #nbrOfDays = datediff(&EFFDT, $BaseDate, 'DAYS')

 

Note that the options available for the return period include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

Example 2: using dateadd() 

Add four weeks to the $dtFinish variable:

let $dtFinish = dateadd($dtStart, 'WEEK', 4)

 

 

PeopleSoft’s Procedures

PeopleSoft has a collection of date math functions as well.  The Format-DateTime function is useful to convert dates between Oracle (native) and external formats for dates.  The other functions are much older.  Utilize the SQR functions instead of the procedures in the DateMath.sqc.

SQL’s use of Dates Time Hidden in a Date

Though in App Designer, you can specify if a field is Date, Time, or DateTime, Oracle stores the field as type DATE (which in Oracle databases, has the Date and Time).  Even though a PeopleSoft field is defined as a Date, Oracle is recording the full Date and Time.  The PeopleSoft page will ensure only Days on the Calendar are entered, even though Oracle will also store the time (if sent).  Normally, PeopleSoft (via SQL) will only send the date, and the Oracle database will default to midnight for the time portion.

If an Insert statement with a Date field is put into a Oracle “Date” field, the time of midnight is the default timestamp.  In other words, if you insert ‘07-JAN-2010’ into an Oracle “Date” field (which always has time), then the Oracle field will hold ‘07-JAN-2010-12:00:00 am’.

To see the time in a field, do the following:

select business_unit, voucher_id

  ,to_char(invoice_dt, 'dd-MON-yyyy hh:mi') Invoice_Date

  ,to_char(time_edit, 'dd-MON-yyyy hh:mi') Time_Edit

  ,to_char(kk_tran_over_dttm, 'dd-MON-yyyy hh:mi') KK_DTTM

from ps_voucher

 

If your SQL is doing a BETWEEN, some rows will be skipped if the Oracle field has time other than midnight.  For example:

The Oracle database has the following three rows of data:

·         07-JAN-2010 7:30 AM

·         09-JAN-2010 12:00 AM

·         10-JAN-2010 3:00 AM

Our SQR statement has the following:

SELECT PYMNT_ID_REF,

       TO_CHAR(PYMNT_DT, 'dd-MON-yyyy hh:mi') PYMNT_DT

FROM PS_PAYMENT_TBL

WHERE PYMNT_DT BETWEEN $FROM AND $TO

 If the fields $FROM and $TO are set as Jan 7 and Jan 10 respectively (without time), then only the first two rows would be retrieved.  The following is the SQL statement with the full breath of what each SQR variable contains:

SELECT PYMNT_ID_REF,

       TO_CHAR(PYMNT_DT, 'dd-MON-yyyy hh:mi') PYMNT_DT

FROM PS_PAYMENT_TBL

WHERE PYMNT_DT BETWEEN ‘07-JAN-2010 12:00 am’ AND ‘10-JAN-2010 12:00 am’

The row with the Jan 10 at 3:00 am will not be retrieved, since 3:00 am is after midnight on the 10th of January.

This should only be an issue if the field you are comparing upon is defined as a Time or DateTime in App Designer.  If the field is only a Date in App Designer, then all comparisons will assume the time is midnight.  In the example above, we had non-midnight timestamps on PYMNT_DT, but in reality, this field is marked as Date only in App Designer.  The only way for a non-midnight timestamp to enter this field is via SQR or SQL.

In the end, it depends on how App Designer has designed the field, be it Date, Time, or Date Time.  Based on this information, correct logic on any SQR or SQL inserts should follow the same logic.  If the field is for Dates only, do not insert/update the field with a time, such as SYSDATE.

 

Last Updated on Saturday, 19 June 2010 03:20
 
Follow us on Twitter
RocketTheme Joomla Templates