Date and DateTime Columns

Top  Previous  Next

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