Solved

Unable to successfully convert date in import scenario

  • 11 August 2022
  • 4 replies
  • 451 views
I
Rank

I have an excel source file which I am trying to import into a journal transaction with a transaction date of 2022-08-11 11:31:31.770

I have left the definition of my data provider field as string, in my import scenario definition I have tried various combinations of ideas from other articles most recently using 

=CDate(Substring([SourceTransactionDate],6,2) + '/' + Substring([SourceTransactionDate],9,2) + '/' + Substring([SourceTransactionDate],1,4))

and when I run my import scenario I get the following error

An error has occurred while the CDate(BinaryOp + (BinaryOp + (BinaryOp + (BinaryOp + (Substring(Identifier(SourceTransactionDate), Const(6), Const(2)), Const(/)), Substring(Identifier(SourceTransactionDate), Const(9), Const(2))), Const(/)), Substring(Identifier(SourceTransactionDate), Const(1), Const(4)))) function was being executed: 'String was not recognized as a valid DateTime.'

Thanks in advance

icon

Best answer by RoyceLithgo 12 August 2022, 03:24

View original

4 replies

R
Rank
Userlevel 5
Badge +2

Substring is indexed from 0 not 1. Try this:

=CDate(Substring([SourceTransactionDate],5,2) + '/' + Substring([SourceTransactionDate],8,2) + '/' + Substring([SourceTransactionDate],0,4))

 

I
Rank

Thank you for this suggestion, sadly I still get an error (albeit a different one) “Object must be of type DateTime”

DConcannon
Rank
Userlevel 6
Badge +2

FWIW, I think you may need to add a format statement. Without it, Acumatica may be expecting Time data too.  

@community If anyone has documented where and how to use format statements, can you share? This piece of info seems to be elusive.

I
Rank

Oops! I had mapped the transaction date to BatchNumber > TransactionDate rather than TransactionDate.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved