Logic help to create date & Time.

[lalitkgehlot89]

Hi, below given data in text format. I want to convert it into Timestamp (DD-MM-YYYY hh:mm:ss) format.

Please help to convert it.

2018/5/30  0:0:0

2018/5/30  0:10:0

2018/05/30  0:10:0

2018/12/30  0:10:0

2018/1/3  0:10:0

2018/1/3  10:10:15

2018/1/23  10:10:15

2018/10/23  10:10:15

[tresesco]

Try like:

=Timestamp(TimeStamp#(Field,'YYYY/M/DD  hh:mm:ss'),'DD-MM-YYYY  hh:mm:ss')

Please note, you have to use exactly same number of spaces in timestamp#() while mentioning format between DD and hh.

[lalitkgehlot89]

Hi Tresesco,

It's not working. whole data is in text format.

[tresesco]

Timestamp#() is being used to convert the text data into proper timestamp (number at the back-end). The concept I showed above is right. If it is not working, that is possibly because the format you are trying with (to convert the text to number) is not matching. you could share a sample file (may be excel or csv).

[lalitkgehlot89]

Hi,

Please get the attached data file. First Column is Date Field.

[tresesco]

It seems to be a proper date as excel reads it so. Try like:

Load

          Alt( TiimeStamp(Field, 'DD-MM-YYYY  hh:mm:ss'),

                    Timestamp(TimeStamp#(Field,'YYYY/M/DD  hh:mm:ss TT'),'DD-MM-YYYY  hh:mm:ss')

              ) as Timestamp

[lalitkgehlot89]

No data is coming in Timestamp field after applying the same condition.

[vvira1316]

Hi,

I think it is happening for two reason. In your data you have multiline header or Unit word in your time column is causing that column to be treated as text column.

You can also try following

SET TimeFormat='hh:mm:ss TT';
SET DateFormat='MM/DD/YYYY';
SET TimestampFormat='MM/DD/YYYY hh:mm:ss[.fff] TT';

Modified input file as follows

LOAD Name,
[GENERATOR MW]
FROM
[CRITICAL PARAMETER LIST_201805300000-201806050000.xlsx]
(ooxml, embedded labels, table is [CRITICAL PARAMETER LIST_2018053]);

//LOAD Name,
// [GENERATOR MW]
//FROM
//[CRITICAL PARAMETER LIST_201805300000-201806050000.csv]
//(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

Output as follows

//LOAD Name,
// [GENERATOR MW]
//FROM
//[CRITICAL PARAMETER LIST_201805300000-201806050000.xlsx]
//(ooxml, embedded labels, table is [CRITICAL PARAMETER LIST_2018053]);

LOAD Name,
[GENERATOR MW]
FROM
[CRITICAL PARAMETER LIST_201805300000-201806050000.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 1 lines);

[tresesco]

This:

LOAD

      Timestamp(TimeStamp#(Unit,'YYYY/M/DD  hh:mm:ss'),'DD-MM-YYYY  hh:mm:ss')as Timestamp,

      Unit

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

worked for me:

[Anonymous]

LOAD Timestamp(Timestamp#(Date,'YYYY/M/D h:m:ss'),'YYYY/M/D h:m:ss') AS Date;