Tech Tip: Importing DATETIME values

PRODUCT: 4D | VERSION: 11.3 | PLATFORM: Mac & Win

Published On: January 7, 2009

DATETIME is a common SQL datatype that contains the date and time in the same field. For example, a DATETIME field can contain the value '2008-12-04 12:34:56'. While, there is no direct datatype within 4D that will accept a date and time value, you can easily import the data by using the CAST function.

CAST is a SQL function that is used to convert an expression to have a specific data type.

This CAST function can be used to convert a DATETIME value to a format that you can work with. For example, we can convert a DATETIME as a string, date, or time value.

For example, the following code connects to an external database and uses the Begin SQL/End SQL syntax to import the DATETIME field "myDT" 3 times, once into a string array, once into a date array, and once into a time array.

ARRAY STRING(40;$myString;0)
ARRAY DATE($myDate;0)
ARRAY LONGINT($myTime;0)

USE EXTERNAL DATABASE("odbc_connection";"username";"password")

Begin SQL
    SELECT CAST(myDT AS Char(40) ), CAST(myDT AS Date ), CAST(myDT AS TIME )
    FROM tester
    into :$myString, :$myDate, :$myTime
End SQL

USE INTERNAL DATABASE

This second example connects to an external database and uses the SQL EXECUTE syntax to import the DATETIME field "myDT" 3 times. This time it imports the data into a string field, a date field, and a time field.

C_STRING(200;$myString;)

USE EXTERNAL DATABASE("odbc_connection";"username";"password")

$myStmt:=""
$myStmt:=$myStmt+"SELECT CAST(myDT AS Char(40) ), CAST(myDT AS Date ), CAST(myDT AS TIME )"
$myStmt:=$myStmt+"FROM myTable"

SQL EXECUTE($myStmt;[Table_1]myString;[Table_1]myDate;[Table_1]myTime)
SQL LOAD RECORD(SQL All Records)

USE INTERNAL DATABASE

Note - The CAST function can only be used to convert data into a data type that exists within the database you are connecting to. For a list of supported data types, please consult the documentation for the database system that you are connecting to.