Working with Database Date | Time Attributes

Introduction

Most databases or applications such as Oracle or Excel allow you to display the date and time in a format of your choice. For Microsoft products, this is often controlled by the system date format. Regardless of how a date/time value displays in the Database tools, the way the underlying date/time value is stored is in a standard syntax specific to that database. For example:

FME will recognize datetime data types for formats that support them. All FME Readers that recognize a datetime data types will convert the dates and times into a common FME datetime syntax. This FME format is database neutral, as shown in the Standard FME Date and Time Format documentation. FME Writer formats that support datetime data types will convert the FME datetime syntax into the appropriate format date time format. 

Once a date or time field has been read into FME, it is treated as a datetime string until it is passed to a writer. All FME Writers also require that the date or time field arrives at the writer in the same common FME datetime syntax i.e YYYYMMDDhhmmss with optional decimal seconds and optional time zone. Then the various format writers correctly write to the database date/time attributes - if their data type is a date/time type.

Simple date / time example: DateTimeCalculation - 2020.fmwt

The Readers and Writers manual has information about the various date or time attributes available in a specific format. For example, see this Microsoft SQL Server user attributes .

If working with Smallworld, please refer to Writing Dates to a Smallworld Database .

Formatting date and time attributes with transformers

To write dates to a database's DATE or DATETIME field, you can use the DateTimeStamper transformer to create a new datetime, or the DateTimeConverter transformer to convert an existing date into the correct format.

For the DateTimeConverter, an Output Format string of %Y%m%d%H%M%S will format attributes into a date/time attribute of the form YYYYMMDDHHMMSS.

An Output Format string of %Y%m%d will format attributes into a date attribute of the form YYYYMMDD.

Selecting dates with SQLCreator or SQLExecutor transformers

You can use DATE and TIME attributes in a select statement within an SQLCreator or SQLExecutor:

Microsoft SQL Server

For example:

SELECT ? FROM Orders WHERE OrderDate='2018-11-11'

You can even use an attribute value; i.e., if the attribute "DATE_VALUE" contains the string 2018-11-11, the select statement becomes:

SELECT ? FROM parcels WHERE data_entrydate='@Value(DATE_VALUE)'

If the value in the attribute is not in a date format that matches the attribute in the database, then it may be necessary to use the DataFormatter to reformat it. For full explanations of working with dates in SQL Server, check the SQL Server documentation.

Oracle

For example:

SELECT ? FROM parcels WHERE data_entrydate=to_date('30-Oct-12','DD-Mon-RR')

You can even use an attribute value; i.e., if the attribute "DATETIME_VALUE" contains the string 10-Jan-31 21:30:00.00000, the select statement becomes:

SELECT ? FROM parcels WHERE order_time = to_timestamp('@Value(DATETIME_VALUE)',’RR-MM-DD HH24:MI:SS.FF’);

If the attribute “DATE_VALUE” contains the string 14-09-31 the select statement becomes:

SELECT ? FROM parcels WHERE order_time = to_timestamp('@Value(DATE_VALUE)',’RR-MM-DD’);

For full explanations of the date function handling in Oracle, check the Oracle SQL documentation.

PostgreSQL/Postgis

For example:

SELECT ? FROM "support"."parcels" WHERE "data_entrydate" ='2014-09-13'

You can even use an attribute value; i.e., if the attribute "date_value" contains the string 10-Jan-31, the select statement becomes:

SELECT ? FROM "support"."parcels" WHERE "data_entrydate" = to_date('@Value(date_value)','DD-Mon-YY')

For full explanations of working with dates in PostgreSQL/Postgis, check the PostgreSQL documentation.

Other Database Formats

The other database formats have similar date and time handling functions. Each format's documentation should be referenced for the correct syntax when building SQL statements that involve working with date attributes.