|Coding For Dates In SQR|
|Written by Buck Vandiver|
|Thursday, 01 April 2010 08:44|
There are three ways to handle dates in SQR:
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:
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:
!date is the reserved variable on the prior line.
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:
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 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
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:
TO_CHAR(PYMNT_DT, 'dd-MON-yyyy hh:mi') PYMNT_DT
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:
TO_CHAR(PYMNT_DT, 'dd-MON-yyyy hh:mi') PYMNT_DT
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|