Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

arpitkharkia
New Contributor II

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:MMSmiley FrustratedS TT

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

  • MM-DD-YYYY
  • HH:MMSmiley FrustratedS

Thanks in advance!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Date/Time format issue

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

16 Replies
MVP
MVP

Re: Date/Time format issue

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

MVP
MVP

Re: Date/Time format issue

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
New Contributor II

Re: Date/Time format issue

Hi Sunny,

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

MVP
MVP

Re: Date/Time format issue

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
New Contributor II

Re: Date/Time format issue

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

Re: Date/Time format issue

Seems works,

Qlikview

Capture.PNG

Excel

Capture.PNG

arpitkharkia
New Contributor II

Re: Date/Time format issue

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

MVP
MVP

Re: Date/Time format issue

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()

MVP
MVP

Re: Date/Time format issue

I am not sure what you mean?