Question from the customer
I have an excel file coming in with a row with a date formatted as DD-MMM. AETL is reading this as a String and not returning the underlying value.
For example it displays as 31-DEC in the workbook and 31-DEC in the AETL transformation designer. We need to format this as a real date and so Reformat Date Transformation action was used with the expectation that it would take the underlying value and return a date as YYYY-MM-DD
However, because it read it as a string it appended the current year onto the string making 31-DEC-2024 where the correct value should have been 31-DEC-2023
Also, have tried "Ignore Format Option" on the reader and this displays the underlying numeric value for the date. Would need to then get this into YYYY-MM-DD format for SQL.
Any way around this?
Answer
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
The easiest way to address this problem is to use calculation transformation function
The following script will convert the number to date
var d: TDateTime;
begin;
d:=StrToInt([F001]);
Result:=d;
end;
Screenshots
Transformation
Calculation
Transformation result
