Answered

Unable to insert into a utcDateTime column due to Time Zone Id

(0) Share

Report

Posted on by Community Member

We recently went down a rabbit-hole trying to insert a field value into a column with the type utcDateTime in AX 2012 R2. We finally figured out what the issue was, which is that SQL maintains a hidden Time Zone Id column (TZID) for every field with type utcDateTime https://msdn.microsoft.com/en-us/library/cc622312.aspx. AX doesn't know about this field, so when I use the following X++:

insert_recordset TestTable (DateTimeValue)

select firstOnly1 ModifiedDateTime

from salesTable;

It tries to insert only the date and time portion of the salesTable.ModifiedDateTime field, but SQL wants a timezone value as well. It throws an error and complains that there are more fields in the insert list than the select list. This is the SQL that is being used when I run the AX code above:

INSERT INTO tempdb."DBO".t104837_B84B43FAB08447ACBA3DFAEB61A658CA (DATETIMEVALUE,DATETIMEVALUETZID,DATAAREAID,RECVERSION,PARTITION)

SELECT TOP 1 T1.MODIFIEDDATETIME,N'tlc',1,5637144576 FROM SALESTABLE T1 WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'tlc')) AND (SALESID=N'LOCALTEST90'))

We can use while select salesTable {salesTable.insert()} instead, but we'd really like to leverage the performance benefits of insert_recordset. Has anyone else encountered this particular problem, and found a fix or workaround?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report

The assumption that the timezone field is generated by SQL Server and AX isn't aware of it is wrong. It's actually created and maintained by AX. Nevertheless you probably found a situation that Microsoft forget to handle, therefore you should report it to them.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (1)

Report

Hi,

In order to achieve what you want you have to set the property 'validTimeStateUpdateMode' from your table. This property accepts values from the base enum 'ValidTimeStateUpdate':

//Set the property for insert_recorset

TestTable.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

//------------------------------------------

insert_recordset TestTable (DateTimeValue)

select firstOnly1 ModifiedDateTime

from salesTable;

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Good idea, but I get the same error result when I set the Time State update Mode as well.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

That's odd, could you please paste your code?.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

static void Job4(Args _args)

{

TestTable testTable;

SalesTable salesTable;

testTable.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

insert_recordset testTable (DateTimeValue)

select firstonly1 modifieddatetime from salesTable;

}

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi,

Ok, you're trying to insert a Date type field (ModifiedDateTime from the salesTable) into a UTC Date type field (UTC DatetimeValue from the test table), won't work.

if you want to use the insert_recordset instruction the target column data type that's going to be stored has to have the same data type that's in the extraction query.

The modifiedDateTime field in the salestable is an audit field that already stores the date and the time in which a record is modified and respects the GMT configuration from your system. So, you could change the type from the testtable to a date type instead of utcdatetime and use the insert_recordset instruction and have the records stored like this:

TestTable

---------------------------------

DateTimeValue (Date Type)

---------------------------------

2015-09-21 11:25:59

instead of

TestTable

--------------------------------------------------

DateTimeValue (UTCDAteTime | TZID

--------------------------------------------------

2015-09-21 | 37001

Unless you have to, really have to, store the modifieddatetime as UTCDateTime, in that case the only option is to insert the records in a while....select loop.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hello,

I have tried this sample but getting the below mentioned error. Could you please help me how to solve this.

static void Job14(Args _args)
{
TestTable testTable;

   SalesTable salesTable;

   testTable.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

   insert_recordset testTable (MOdified)

       select firstonly1 modifieddatetime from salesTable;

}

Error details:

Cannot execute a data definition language command on TestTable (TestTable).
The SQL database has issued an error.

SQL statement: INSERT INTO tempdb."DBO".t106029_6D93C6BAD34647A99AF7DADDE8AF3A52 (MODIFIED,MODIFIEDTZID,DATAAREAID,RECVERSION,PARTITION) SELECT TOP 1 T1.MODIFIEDDATETIME AS f1,N'del' AS DATAAREAID,1 AS RECVERSION,5637144576 AS PARTITION FROM SALESTABLE T1 WHERE ((PARTITION=5637144576) AND (DATAAREAID=N'del'))

static void Job14(Args _args)
{
TestTable testTable;

   SalesTable salesTable;

   testTable.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);

   insert_recordset testTable (MOdified)

       select firstonly1 modifieddatetime from salesTable;

}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Hi,

we have the same error and opened an MS request to get an solution. The exam result you'll find below.

It's only a workaround and the needed kernel fix might come or not :-).

The behaviour of the TZID fields is explained here:

https://msdn.microsoft.com/en-us/library/cc622312.aspx

As the article mentions, the hidden TZID field is only added for AOT fields:

When you use the Application Object Tree (AOT) to add a DateTime field named MyDateTime to a table, the following two fields are added to the database table:

MyDateTime

MyDateTimeTZId

So the field is not added for system fields that don’t show up in the AOT, such as ModifiedDateTime.

If you test with a table that actually has an AOT DateTime field, the insert works fine, for instance:

  BatchJob   custTable;

   insert_recordset testTable (Key, TimeStamp)

        select Caption, StartDateTime from custTable;

The problem occurs at kernel level when the insert_recordset statement is generated, since we need to write three fields in TestTable (Key, TimeStamp and TimeStampTZID) but we don’t have the same fields in CustTable, only the first two.

The question now is what the business impact is and it all revolves around how this code is used in production. A kernel fix in this area is very risky and the benefit is not very clear until we know if the workaround is viable or not.

If this functionality is needed, we can use the following workaround. The downside is a possible reduction in performance so if that’s the case, we need to know what the performance is on the customer’s side.

static void _TestScriptFix(Args _args)

{

    _TestTable testTable;

    CustTable   custTable;

    if ((select firstOnly RecId from CustTable).RecId == 0)

        throw warning("Create some customers before running this test!");

    ttsBegin;

    delete_from testTable;

    insert_recordset testTable (Key)

        select AccountNum from custTable;

    ttsCommit;

    info("The Key column has been successfully inserted.");

    ttsBegin;

    delete_from testTable;

    while select AccountNum, ModifiedDateTime from custTable

    {

        testTable.Key = custTable.AccountNum;

        testTable.TimeStamp = custTable.modifiedDateTime;

        testTable.insert();

    }

  /*insert_recordset testTable (Key, TimeStamp)

        select AccountNum, ModifiedDateTime from custTable;*/

    ttsCommit;

    info("The Key AND TimeStamp column has been successfully inserted.");

}

Was this reply helpful? Yes No