topic Time format in QlikView

Time format

Fri, 04 Aug 2017 11:10:31 GMT

Hi,

I have couple of data sources containing different time formats.

I unsuccessfully tried different different ways to format them as i need... maybe you can help me?

One looks like: "2016-05-01 00:45:00+02:00" and i need to split it in date and time.

best,

Re: Time format

sunny_talwar — Fri, 04 Aug 2017 11:16:34 GMT

May be this

Date(Floor(TimeStamp#(Left(DateTimeField, 19), 'YYYY-MM-DD hh:mm:ss'))) as Date,

Time(Frac(TimeStamp#(Left(DateTimeField, 19), 'YYYY-MM-DD hh:mm:ss'))) as Time,

Re: Time format

PrashantSangle — Fri, 04 Aug 2017 11:19:54 GMT

If dates are in different format then check alt() in Help menu.

might help you.

If need more help then some sample data will help us to give better solution.

Regards,

Re: Time format

ahaahaaha — Fri, 04 Aug 2017 11:21:22 GMT

Hi,

Try, as variant

Date(Timestamp#(Trim(SubField(F, '+', 1)), 'YYYY-MM-DD hh: mm: ss'))

and

Time(Timestamp#(Trim(SubField(F, '+', 1)), 'YYYY-MM-DD hh: mm: ss'))

Re: Time format

Fri, 04 Aug 2017 11:40:06 GMT

Thank you very much!

Re: Time format

Fri, 04 Aug 2017 11:42:32 GMT

Thank you Andrey,

Prashant 's solution works, i don't know why your is not but i really appreciate your help.

Re: Time format

hugmarcel — Mon, 07 May 2018 20:34:53 GMT

Use these functions for any known Date Format:

'Formats any DateTime based on any date format ("YYYYMMDD_HHMISS", "DD.MM.YYYY HHhMImSSs", etc)

'Pseudocode:
Function GetDateTime(theDateTime, theFormat)
vFormDT     = theFormat
vFormDT     = Replace(vFormDT,"YYYY",AZ(Year(theDateTime),4))
vFormDT     = Replace(vFormDT,"MM", AZ(Month(theDateTime),2))
vFormDT     = Replace(vFormDT,"DD", AZ(Day(theDateTime),2))
vFormDT     = Replace(vFormDT,"HH", AZ(Hour(theDateTime),2))
vFormDT     = Replace(vFormDT,"MI", AZ(Minute(theDateTime),2))
GetDateTime = Replace(vFormDT,"SS", AZ(Second(theDateTime),2))
End Function

'LPad with "0"
Function AZ(num,pos)
AZ = LeftPad(num, "0", pos)
End Function