Timestamp format error

[kmswetha]

I'm having a file name where I should be extracting timestamp from the file.

However I'm able to convert to date but not time stamp

My file format is XYZ_20_11_2016_00_12

by using string functions I'm able to extract date, 11/20/2016 but its throwing error for time stamp.

I'm using date(date#)) but it is not working for timestamp(timestamp#))

any suggestions

[tresesco]

One correction.

M-Month

m-Minute

=timestamp(timestamp#(right('XYZ_20_11_2016_00_12',16),'DD_MM_YYYY_HH_mm'),'M/D/YYYY HH:mm:SS tt')

[PrashantSangle]

Can you provide us what are you trying.

One more thing in format MM - Repesent Month

mm-repesent minutes.

Regads,

[MarcoWedel]

Hi,

another example:

LOAD SomeField,
    Timestamp(Timestamp#(Mid(FileBaseName(),Index(FileBaseName(),'_',-5)+1),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm') as FileNameTimestamp,
    FileBaseName() as FileBaseName
FROM [*XYZ*.txt] (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

hope this helps

regards

Marco

[rubenmarin]

Hi Swe, seems the problem is with the filename, how are you retrieving and using the filename?