Date field not recognized with resident load

[SerSwagster]

Hi everyone,

I have to load some sheets with a field called input_date , which records have this format:

2021-10-25T07:07:58.322

To load this date field in the way a desired, I used this code:

date(date#( LEFT(input_date ,10) & ' ' & mid(input_date ,12,8),'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as input_date 

In this way, the field is loaded correctly and recognized as a date.

But subsequently, for other needs, I had to load this column in a different way: I loaded an empty table with column names,  i made a concatenate load * to fill the table, and finally I created a new table using the previous as resident, to do my operations (as the one before mentioned for input_date). Unfortunately, this time, although input_date field was loaded with the same code line, it was no more recognized as a date.

Can somebody explain me why this happens, and solve the problem?

Thank in advance.

[JustinDallas]

Can you show us an example of your code? It's hard to visualize what might be going on in your various loads.

[SerSwagster]

First attempt (date is recognized):

For each vFileName in Filelist ('hello_*.xlsx')
Concatenate(A)
LOAD
*
FROM
[$(vFileName)]
(ooxml, embedded labels, table is [Sheet 1]);
Next vFileName

Second attempt (date is not recognized):

A:
LOAD * INLINE [

a, b, c, input_date

];

For each vFileName in Filelist ('hello_*.xlsx')
Concatenate(A)
LOAD
*
FROM
[$(vFileName)]
(ooxml, embedded labels, table is [Sheet 1]);
Next vFileName

User:
LOAD a,
b,
c,
date(date#( LEFT(input_date,10) & ' ' & mid(input_date,12,8),'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as input_date
Resident A;
Drop Table A;

[marcus_sommer]

I would load input_date a second time without any transforming within the user-table and looking on how the field-values look like. Then the transforming might be adjusted and/or the reason why it looked differently may become visible and the logics before might be changed.

- Marcus