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

[kusumanchir]

Hi Swe,

Please try using this,

=timestamp(timestamp#(replace(mid('XYZ_20_11_2016_00_12',5,10),'_','/')&' ' & replace(mid('XYZ_20_11_2016_00_12',16,5),'_',':'),'DD/MM/YYYY hh:mm'))

[tresesco]

How are you using timestamp(Timestamp#(... ; what is it giving you as output and what do you expect?

[kmswetha]

it's not giving anything.In textbox it's just showing -

[Miguel_Angel_Baeyens]

Which expression are you using and what format code are you providing?

[PrashantSangle]

Try simple

=Timestamp(Timestamp#(mid('XYZ_20_11_2016_00_12',index('XYZ_20_11_2016_00_12','_')+1),'DD_MM_YYYY_hh_mm'))

Replace XYZ_20_11_2016_00_12 with your fieldName.

Regards

[Anil_Babu_Samineni]

Try this, May be?

=Date(Date#(Purgechar(Left(Replace(KeepChar('XYZ_20_11_2016_00_12', '0123456789_'),'_', '/'),11),'/'),'DDMMYYYY'),'MM/DD/YYYY')

[tomasz_tru]

If the string is the same lenght all the time:

timestamp(timestamp#(right(FIELD,16), 'DD_MM_YYYY_HH_MM'),'YYYY-MM-DD HH:MM:SS') as ts

[kmswetha · Accepted solution]

surprise part is, if I hardcode and do its working

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

but not if I replace file name (

[kmswetha]

I did exactly same, it works but if replace FIELD with actual name its not working