Skip to main content
Solved

Unable to successfully convert date in import scenario

  • August 11, 2022
  • 4 replies
  • 685 views
I
Freshman II

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

Best answer by RoyceLithgo

Substring is indexed from 0 not 1. Try this:

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

 

K

4 replies

R
Pro II
Forum|alt.badge.img+3
  • RoyceLithgo
  • Pro II
  • Answer
  • August 12, 2022

Substring is indexed from 0 not 1. Try this:

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

 

Kandy Beatty
Chris Hackett
I
Freshman II
  • iacook28
  • Author
  • Freshman II
  • August 12, 2022

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

DConcannon
Varsity II
Forum|alt.badge.img+2
  • DConcannon
  • Varsity II
  • August 15, 2022

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.

Kandy Beatty
Chris Hackett
I
Freshman II
  • iacook28
  • Author
  • Freshman II
  • August 19, 2022

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

Terms & ConditionsCookie settingsAccessibility statement
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