In the IUIE, how are date ranges handled in a data extract?

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

Note: 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 2015-03-05 00:00:00.

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.