Date/Time format issue

[arpitkharkia]

Hi all,

I have a timestamp field which consists date of the following two formats:

• MM-DD-YYYY HH:MM
• MM/DD/YYYY HH:MM:SS TT

I want to extract date and time separately in the following formats:

• MM-DD-YYYY
• HH:MM:SS

Thanks in advance!

[sunny_talwar]

May be like this

Date(Floor(Alt(

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm')),

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm:ss TT'))

)), 'MM-DD-YYYY') as Date,

Time(Frac(Alt(

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm')),

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm:ss TT'))

)), 'hh:mm:ss') as Time

[Miguel_Angel_Baeyens]

To read the value with that specific mask provided it is not recognized by Qlik, you can use Date#():

LOAD
...
// Note the minutes are lowercase "mm" because uppercase "MM" are intended for months
Date(Date#(DateField1, 'MM-DD-YYYY hh:mm')) AS DateField1,
Date(Date#(DateField2, 'MM/DD/YYYY HH:mm:ss TT')) AS DateField2,
...

To display the values you can also use the Date() function with different masks, or create two different fields in the data model, using the same logic as above.

[arpitkharkia]

Hi Sunny,

Its not working for me. I have attached the file. If possible please check!

[sunny_talwar]

For the sample attached, this worked

Table:

LOAD Date(Floor(Call_Date), 'MM-DD-YYYY') as Date,

  Time(Frac(Call_Date), 'hh:mm:ss') as Time

FROM

[..\..\..\Downloads\CallDate.xlsx]

(ooxml, embedded labels, table is Sheet1);

[arpitkharkia]

if you observe that dates after 11/2/2017 are not being considered. How to handle that?

[Anil_Babu_Samineni]

Seems works,

Qlikview

Excel

[arpitkharkia]

If I am right all the dates are not of FEB. The file I have attached is only for Feb. How to handle that?

[tresesco]

The below worked:

Date(Floor(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT')

)), 'MM-DD-YYYY') as Date,

Edit: removed unnecessary num()

[sunny_talwar]

I am not sure what you mean?