Dynamics AX - Native ODBC SMALLDATETIME update

Overview

Updating a SQL-SMALLDATETIME field with ODBC is far away from X++ code in Dynamics AX.

Issue

If a smalldatetime field should be set with ODBC the errorlog
"[Microsoft][ODBC SQL Server Driver][SQL Server] The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value." can be thrown and stops further critical functionality.

Solution

A working solution to define the value in ISO 8601 is to use the capital letter T between DATE and TIME values.[1][2]

The format goes like this 'YYYY-MM-DDThh:mm:ss'.

Example

The exmple listing shows an ODBC-querystring.

str sqlUpdate = "UPDATE [DATABASE].[dbo].[TABLE]"

            +   " SET DATEFIELD = '2015-08-28T22:08:54'";

The string can be sent to the Statement.executeUpdate(updateSQL) classmethod to update the DATEFIELD (ODBCconnection class has to be set first). 

Sources:
[1] 28.08.2015 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42d5b895-3767-4775-a18e-21ac8485b37a/update-smalldatetime-column
[2] 28.08.2015 http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Underline:
YYYY - year 4 dgits
MM - month 2 digits
DD - day 2 digits
hh - hour 2 digits
mm - minute 2 digits
ss - second 2 digits