Hope I can get some help with this. Using an export scenario to create a CSV file, data is populated from a GI. Dates in the the GI are shown without a timestamp but when the data is prepared, any date field has a timestamp. Is there anyway to stop that being created, my customer is using the CSV for a file upload to their bank and it fails on the timestamp.
Solved
Export scenario is creating date field with timestamp
Best answer by Ankita Tayana
Hi
Acumatica stores all DateTime fields in the database, even if the GI displays only the date. When exporting, it defaults to the full ISO format unless you override it. Modify the GI to include a calculated field that explicitly formats the date as a string, stripping the timestamp.
This converts the DocDate into a string with no timestamp, e.g., 2025-05-05
=Format([Table.DateField], "yyyy-MM-dd")
if your bank requires a different format (e.g., MM/dd/yyyy); just update the format string accordingly.
Hope this helps!
+3Instead of exporting the date file directly in your scenario, it sounds like you first should apply some formatting to turn it into a string that will look and behave like a date in the CSV. Something like:
=Format('{0:MM/dd/yyyy}', [adjustment.TranDate])
will reformat ‘5/8/2025 12:00:00 AM’ into ‘08/5/2025’. Just change the date format to what your customer’s bank is expecting to receive.
Hi
Acumatica stores all DateTime fields in the database, even if the GI displays only the date. When exporting, it defaults to the full ISO format unless you override it. Modify the GI to include a calculated field that explicitly formats the date as a string, stripping the timestamp.
This converts the DocDate into a string with no timestamp, e.g., 2025-05-05
=Format([Table.DateField], "yyyy-MM-dd")
if your bank requires a different format (e.g., MM/dd/yyyy); just update the format string accordingly.
Hope this helps!
+3A good point, the Format function is available in the Report Designer. You’d have to export a report as Excel to get close to a CSV, instead of an Export Scenario. Instead, you can piece together the parts of a date to get the format you need using Year(), Month(), Day():
=Concat(Year([Table.DateField]), '-', Month([Table.DateField]), '-', Day([Table.DateField]))
If you’re desperate for leading 0s on single digits and this isn’t working for you,
...Right( Concat(‘00’, Month([Table.DateField])), 2)…
Manually formatting strings isn’t the most elegant process sometimes, but there are the tools to do it.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.