Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

Date/Time format issue

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!

1 Solution

Accepted Solutions
sunny_talwar

Here you go... try this

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss TT';

Table:

LOAD Call_Date,

  Date(Alt(Date#(Date(Num#(Text(Call_Date))), 'D/M/YYYY'), Date(Floor(Call_Date))), 'MM-DD-YYYY') as Date,

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

FROM

[CallDate.xlsx]

(ooxml, embedded labels, table is Sheet1);


Capture.PNG

View solution in original post

16 Replies
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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

Anil_Babu_Samineni

Seems works,

Qlikview

Capture.PNG

Excel

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
arpitkharkia
Creator III
Creator III
Author

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
MVP
MVP

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,


Capture.PNG

Edit: removed unnecessary num()

sunny_talwar

I am not sure what you mean?