topic Re: Help Required for date format conversion in App Development

Help Required for date format conversion

ABP2021 — Fri, 01 Aug 2025 11:57:05 GMT

Could you please help me with converting a date from

Format 20250731T233347.455+1000

Format 2024-11-20 19:38:02
If you know the steps or QV logic to achieve this, kindly let me know.

Appreciate your help in advance!

Re: Help Required for date format conversion

Oleg_Troyansky — Fri, 01 Aug 2025 13:18:25 GMT

Hi there,

I'm not familiar with the first format to the fullest, but it looks like it starts from the date YYYYMMDD, then the letter T, and then the time hhmmss. After that, I can only presume that 455 represent milliseconds, and I'm not sure what +1000 stands for.

So, assuming that you only need your timestamps to be stored up to the second level, I'd do something like this:

date(

date#( left(MyDate, 15) // only extract the first 15 symbols

, 'YYYYMMDDThhmmss') // this is the source date/time format

, 'YYYY-MM-DD hhmmss) // this is the target date/time format

Something like this should work. I never tried converting timestamps with the letter T in them, though. So, if this doesn't work, then you might have to parse the string

Cheers,

Oleg Troyansky

Re: Help Required for date format conversion

ABP2021 — Mon, 04 Aug 2025 11:08:37 GMT

Hi Troyansky,

Thanks for your reply. basically, after version upgrade the date format of log is changed. that we wanted to update like earlier.

Re: Help Required for date format conversion

Kaushik2020 — Tue, 05 Aug 2025 13:03:29 GMT

you can try to format it via load editor may be with something like below.

// Sample inline data
Temp:
LOAD * INLINE [
RawTimestamp
20250731T233347.455+1000
];

// Process the timestamp
Final:
LOAD
RawTimestamp,

// Extract parts
Date(Mid(RawTimestamp, 1, 8), 'YYYYMMDD') as DatePart,
Time#(Mid(RawTimestamp, 10, 6), 'hhmmss') as TimePart,
Left(Mid(RawTimestamp, 17, 3), 3) as MilliSec,
Mid(RawTimestamp, 20, 5) as TimezoneOffset,

// Convert to UTC datetime (adjusting for timezone)
Timestamp(
Timestamp#(Mid(RawTimestamp, 1, 😎 & ' ' & Mid(RawTimestamp, 10, 6), 'YYYYMMDD hhmmss')
- (Num(Mid(RawTimestamp, 20, 3)) / 24)
) as UTC_Timestamp,

// Format in final required format
Date(Date#(Mid(RawTimestamp, 1, 8), 'YYYYMMDD')) & ' ' &
Time(
Timestamp(
Timestamp#(Mid(RawTimestamp, 1, 😎 & ' ' & Mid(RawTimestamp, 10, 6), 'YYYYMMDD hhmmss')
- (Num(Mid(RawTimestamp, 20, 3)) / 24)
), 'hh:mm:ss'
) as FinalFormattedTimestamp

Resident Temp;
DROP TABLE Temp;

Pls convert these emoji to 8 ) as mentioned in the below image. Seems it is auto changing this. see below image with highlighted text.

Re: Help Required for date format conversion

antose — Wed, 06 Aug 2025 14:10:48 GMT

tmp:
LOAD * INLINE [
RawTimestamp
20250731T233347.455+1000
];

NoConcatenate
Final:
LOAD RawTimestamp,
timestamp(Timestamp(Timestamp#(Replace(Left(RawTimestamp,15),'T',' '), 'YYYYMMDD hhmmss')),'YYYY-MM-DD hh:mm:ss') AS FinalTimestamp
RESIDENT tmp;

DROP TABLE tmp;

// FinalTimestamp = 2025-07-31 23:33:47