Convert a timestamp list box in individual boxes of year month day

[alexasso-09]

Hi to all, 

quick question, 

qvw that takes data from a folder with 2.000+ csv files, all the 2000 files with the same columns.

One of those columns is a timestamp  2020-05-20T00:00:00

The script very simple,  LOAD * from C:\editestfiles\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);

2 problems:

1.I need to split the time stamp to individuals list boxes , year , month , day 

2. need to overpass the problem of explicit labels since all files don't have headers I need to create them.

thanks in advance for the help 

[Lisa_P]

Do you have a sample of the data ?

[berndjaegle]

Hi Alexasso,

1) convert your timestamp to date like doing this:

Date(Floor( Date#(DATE_FIELD)),'MM.DD.YYYY hh:mm') as DATE

2) use date functions like

year(DATE)

moth(DATE)

etc.

BR,

Bernd

[alexasso-09]

there you go , attached the file 

column number 21

[Taoufiq_Zarra]

for the first problem:

Maye be :

load Year(@2) as year,Month(@2) as Month, Day(@2) as Day,*  from C:\sampleedi\*.csv (txt, utf8 , explicit labels, delimiter is ';', msq);

for the second :

can you give an exemple of the source file (csv)? and the expected output ?

[alexasso-09]

not working Taoufiq 

[NitinK7 · Accepted solution]

Hi,

try below , may be it is help

load *,
Year(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myyear,
Month(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as mymonth,
day(Date(Date#(SubField(@21,'T',1),'YYYY-MM-DD'),'DD/MM/YYYY')) as myday ;

LOAD *
FROM
TB01_20200703_173947.xlsx

or

Year((Timestamp#(@21))) as myyear,
month((Timestamp#(@21))) as mymonth,
day((Timestamp#(@21))) as myday