| Navigation: SQL Drivers >====== Date and DateTime Columns ====== |    |
A common practice in some SQL databases (MSSQL, Oracle, and others) is to define a composite DateTime column (i.e., one column representing two pieces of data). In order for Clarion to separate the date and time information, upon an import of a Table with a DateTime data type column into the Dictionary Editor results in the following type of data structure:
Orderdate STRING(8) !original column name from SQL
Orderdate_GROUP GROUP, OVER(Orderdate) !structure created by Clarion
Orderdate_DATE DATE !use this column to reference date info
Orderdate_TIME TIME !use this column to ref time info
END
No matter what type of SQL/ADO/ODBC driver you are using, Clarion will detect and convert these composite DateTime columns for you automatically.
MSSQL TIMESTAMP fields are now flagged as READONLY by the Dictionary Synchronizer.
Know your back end! For example, the SMALLDATETIME and DATETIME data types of MS-SQL are treated equally, with both only being able to store the minimum of either the precision of the Clarion TIME field or the backend data type. So, in the case of the SMALLDATETIME data type, the seconds and hundredths of a second are discarded, using the SMALLDATETIME rule that > 29.99 is rounded up to the next minute.
Another note; If you don't need the time portion, you can just use a “Date” type field (as long as no one else is writing to the “Time” portion from another application). Otherwise, you
will need to be aware that the time portion is not zero when filtering, sorting, etc.
If your Application does not use the time part of a timestamp field then you can (optionally) change the Clarion column definition from:
TSField STRING(8)
TSFieldGroup GROUP,OVER(TSField)
DateFld DATE
TimeFld TIME
END
to
TSField DATE
Synchronizer Considerations
When using the dictionary synchronizer, the READONLY attribute will be set by the MSSQL synchroniser for UNIQUEID and TIMESTAMP fields only. The SQL Anywhere synchronizer sets it for fields that have a default value of TIMESTAMP.
ODBC Extended Time Information
If you define a single TIME field in your Clarion file definition, the ODBC driver (and all ODBC based drivers) will use the ODBC TIME_STRUCT structure to get and set the field. This structure only supports passing the hours, minutes and seconds.
If your backend stores more information than this in the time field (i.e., fractions of a second), you must use a timestamp structure to access the field for accurate indexing. When a timestamp structure is used, then the driver uses an ODBC TIMESTAMP_STRUCT to communicate with the backend.
Example:
Orderdate STRING(8) !original column name from SQL
Orderdate_GROUP GROUP, OVER(Orderdate) !structure created by Clarion
Orderdate_DATE DATE !use this column to reference date info
Orderdate_TIME TIME !use this column to ref time info
END