Date End Field
In PeopleSoft the DATE_END field is defined as a date field. If the date 12/31/2009 is selected for this field it will show up like 2009-12-31 00:00:00. On 12/31/09 if you are comparing this field to SYSDATE via SQL your results might not return as expected since SYSDATE would return 2009-12-31 10:42:18 (i.e. the timestamp as well). To get around this you can use the trunc() function to to output trunc(SYSDATE) in a format that compares nicely with your data.
SELECT EOEP_RULE_ID, DATE_BEGIN, DATE_END
FROM PS_EOEP_FORMULA
WHERE DATE_BEGIN <= trunc(SYSDATE) AND DATE_END >= trunc(SYSDATE)
| EOEP_RULE_ID | DATE_BEGIN | DATE_END |
|---|---|---|
| 10001_05_EXTENDED | 2009-01-01 00:00:00 | 2009-12-31 00:00:00 |
| 10001_05_EXTENDED | 2009-01-01 00:00:00 | 2009-12-31 00:00:00 |
| 10001_05_EXTENDED | 2009-01-01 00:00:00 | 2009-12-31 00:00:00 |
| 10001_05_EXTENDED | 2009-01-01 00:00:00 | 2009-12-31 00:00:00 |
No Comments