Omnis Technical Note TNSQ0002

Inserting Dates into a Server from Omnis Studio

For Omnis Studio
By Ian Thurbon

There have been several reports of difficulties inserting dates from Omnis Studio into a server.

The example library below contains code on how to insert dates and times into each server. There is
a description of the test within the library.

The example library included with this technical note inserts and retrieves different types of Omnis date and time datatypes into different types of server date datatypes. This example is generic and can be run against any of the Omnis DAMS. Just add your logon details to the method within.

To see the type of server datatype created for the relevant Omnis datatype check the value of the lSqlScript local variable at the end of the createtable() method.

The values inserted into the database are generated from #D but you can change the value to see the effect it has when inserting or retrieving the date.

NOTE: do not attempt to insert empty date values into any server date datatype as this will generate an error. Most servers will insert a default value if you attempt to insert empty dates into a date field, so in effect it will no longer be empty. If you wish to insert dates with no value then use NULL's.
These work correctly in conjunction with Omnis.

Download the sample library (sqldates.zip)