Date Considerations

You can avoid confusion when using meta-SQL such as %Datein and %Dateout if you remember to use "in" functions in the Where subclause of a SQL query and to use "out" functions in the Select (main) clause of the query. For example:

select emplid, %dateout(effdt) from ps_car_alloc  a where car_id = '" | &REGISTRATION_NO | "' and plan_type = '" | &PLAN_TYPE | "' and a.effdt = (select max (b.effdt) from ps_car_alloc b where a.emplid=b.emplid and b.effdt <= %currentdatein) and start_dt <= %currentdatein and (end_dt is null or end_dt >= %currentdatein)";

Use date or time wrappers (%Datein, %TimeOut, and so on) when selecting date or time columns into memory. Different database platforms use different internal formats for these data types. Those different formats range from 1900-01-01 to 01-JAN-1900. DateTime (timestamp) formats are even more complex.

In PeopleCode (SQLExecs and the like), use both an "out" wrapper when selecting a DateTime value into memory, as well as an "in" wrapper when referencing the value as a bind variable.

In an Application Engine program, when you populate a DateTime state field in a %Select, you still must use an "out" wrapper to get the value into the standard format. But when you reference this state field in a %Bind, Application Engine automatically provides the "in" wrapper around the substituted literal or bind marker (the latter if reuse is in effect).

Actually, if you use the code %Bind(date) in the select list of another %Select statement, to load the value into another date field, Application Engine doesn't provide a wrapper (since you are selecting a value that is already in the standard format, you do not need to use a wrapper).

Dynamic views containing Date, Time, or DateTime fields must be wrapped with the appropriate meta-SQL. PeopleTools uses the SQL directly from the view definition (view text) and doesn't generate anything, so no meta-SQL wrapping is done.

SQL views should not contain meta-SQL that wraps Date, Time, or DateTime fields.

In SQR, if you are using {DateTimein-prefix}, and so on, you need to do the following:

• For string or let statements when using dynamic SQL, you need to use the following:

   {DYN-Date***in/out-prefix/suffix}

• For SQL statements, you need to use the regular SQL, as follows:

  {Date*** in/out-prefix/suffix}