Time and Date Attributes are among the more tricky to get into, and out of, a database.
Microsoft SQL Server
DateTime fields represent date and time data from January 1, 1753 to December 31 9999.
For example, a value of 20061231235959 represents 11:59:59PM on December 31, 2006.
When writing to the database, the writer expects the date attribute to be in the form
YYYYMMDDHHMMSS
SmallDateTime fields represent date and time data from January 1, 1900 to June 6, 2079.
For example, a value of 20060101101000 represents 10:10:00AM on January 1, 2006.
When writing to the database, the writer expects the date attribute to be in the form
YYYYMMDDHHMMSS.
IBM Informix
The Informix reader returns two attributes for each DATE field.
The first attribute has the name of the database column, and the form YYYYMMDD.
The second attribute has a suffix of .full and is of the form YYYYMMDDHHMMSS.
For example, if the date field is called UPDATE_DATE, the attributes are UPDATE_DATE and
UPDATE_DATE.full
The Informix writer looks for both attributes when a DATE or DATETIME column is being output.
Either may be specified. If both attributes are specified, then <name>.full takes precedence.
IBM DB2
As per the IBM Informix reader/writer except it supports DATE, TIME and TIMESTAMP types.
ESRI ArcSDE
As per the IBM Informix reader/writer
Oracle
Oracle expects DATE values in the format YYYYMMDDHHMMSS even though when you display
a date field from an Oracle table it shows something like this: 01-JAN-08 12:00:00
Formatting Date Attributes with Transformers
To write dates to a database DATE or DATETIME field you can use the TimeStamper or
DateFormatter transformer to get the date into the correct format.
A format string of ^Y^m^d^H^M^S will return a date-time in the form YYYYMMDDHHMMSS
A format string of ^Y^m^d will return a date in the form YYYYMMDD
Selecting Dates with the SQLExecutor
When using DATE format attributes in a select statement within an SQLEXecutor you can use a statement such as the following
Oracle
select * from parcels where data_entrydate=to_date('31-Mar-10');
You can even use an attribute value - i.e. Attribute "DATE_VALUE" contains the string 10-Jan-31 then the select statement becomes
select * from parcels where data_entrydate=to_date('@Value(DATE_VALUE)');
Microsoft SQL Server
SELECT * FROM Orders WHERE OrderDate='2010-11-11'
You can even use an attribute value - i.e. Attribute "DATE_VALUE" contains the string 2010-11-11 then the select statement becomes
select * from parcels where data_entrydate=to_date('@Value(DATE_VALUE)');