AX 2012 insert_recorset with utctime field

Hy,

I’m trying to insert a great amount of data into a tmpTable (for a DataProvider)

something like this:

Insert_recordset tmpTable(AccountingDate, CreateDate, JournalNumber)

select AccountingDate, CreatedDateTime, JournalNumber

from generaljournalentry

where generaljournalentry.AccountingDate != generaljournalentry.CreatedDateTime

the where clause return error.

if I try to make the convertion also return error

where generaljournalentry.AccountingDate != DateTimeUtil::date( generaljournalentry.CreatedDateTime)

if i remove the where clause i also have errors when excecute the DataProvider in Visual studio, asking for CreateDateZID

I already try a while select but for the amount of data the query takes to long more than ten minutes, unaceptable for a report, if i excute the query without the CreateDate field runs in less than 40 seconds.

I try to create a Query but same thing when inserting to tmpTable (takes too long)

I hope somebody can help me (sorry for the grammar and syntax, english is my foreing language)

Thanks

So your problem is not related to insert_recordset, the problem is in the select statement. You’re trying to write code like this:

GeneralJournalEntry entry;
select entry
    where entry.AccountingDate != entry.createdDateTime;

And get a compilation error saying that “Operand types are not compatible with the operator”. This should have already been mentioned in your question…

Your attempt with DateTimeUtil::date() must fail as well, because there is no way how SQL Server could call the method when evaluating the where clause.

Now tell us how do you want to compare AccountingDate with the UTC date in createDateTime. Are you happy with considering AccountingDate as a date in UTC, or do you need some time zone conversion before the comparison?

Thanks for answer.

I need time zone conversion, because i need to find all transactions posted on a different date to the posting date.

Okay, so that’s something what missed in your original attempts. Which timezone do you want to use?

Gmt -4.

Thanks

Always UCT-4, regardless of any context? Or is there actually something that defined that, such as the value in the *Tz field, user’s preferred timezone or so? Also, should the offset be the same even if the location in question applies daylight savings time?

Yes always utc -4,

I think the easiest way is to create a view and do the conversion in a computed column (of type date). Or maybe you can do even the comparison in a computed column and return just a flag indicating whether the record meets your criteria.