IUIE date ranges in data extracts

Date fields in IUIE data extracts (datagroup queries) have been updated to handle comparison and range operators. The following operators are now active with date fields:

Operator Explanation
Sample user input in "Payroll period end date" field
Resulting query
 
No operator implies a test for equality. The specified value is converted to a date and compared for equality with the database value.
09/15/2006

It is neither valid nor equivalent to enter =09/15/2006.

select EMP_TSHT_PAYEND_DT
from DSS.TK_PYRL_HRS_GT
WHERE (EMP_TSHT_PAYEND_DT = to_date('09/15/2006','MM/DD/YYYY') )
  AND ( RowNum <= '10')
 
<
Less than: The specified value is converted to a date and compared with the database value. Returns are less than (i.e., earlier than) the specified value.
<10/16/2006
select EMP_TSHT_PAYEND_DT
from DSS.TK_PYRL_HRS_GT
WHERE (EMP_TSHT_PAYEND_DT < to_date('10/16/2006','MM/DD/YYYY') )
  AND ( RowNum <= '10')
>
Greater than: The specified value is converted to a date and compared with the database value. Returns are greater than (i.e., later than) the specified value.
>10/16/2006  
<=
Less than or equal to: The specified value is converted to a date and compared with the database value. Returns are less than or the same as (i.e., earlier than or the same date as) the specified value.
<=10/16/2006  
>=
Greater than or equal to: The specified value is converted to a date and compared with the database value. Returns are greater than or the same as (i.e., later than or the same date as) the specified value.
>=10/16/2006  
..
A range between two dates: Each of the supplied values is converted to a date and compared with database values. The database value must be between the two dates to be selected.
09/15/2006..10/01/2006
select EMP_TSHT_PAYEND_DT
from DSS.TK_PYRL_HRS_GT
WHERE (EMP_TSHT_PAYEND_DT 
       between to_date('09/15/2006','MM/DD/YYYY') 
       and     to_date('10/01/2006','MM/DD/YYYY') )
!
NOT (negation): This can be used as a prefix for any of the operators above with the exception of the date range operator.
!09/15/2006
select EMP_TSHT_PAYEND_DT
from DSS.TK_PYRL_HRS_GT
WHERE (NOT EMP_TSHT_PAYEND_DT = to_date('09/15/2006','MM/DD/YYYY') )


This is document alog in the Knowledge Base.
Last modified on 2019-02-14 14:17:22.

Contact us

For help or to comment, email the UITS Support Center.