Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

datalakeyu
Valued Contributor

How to convert time string to date or time stamp?

I get a time string as the following. I would like to convert it to date (mm/dd/yyyy) or time stamp.

I tried time(myTime), date(myTime), but does not work.

Could you please give any suggestion?

Thanks!


myTime 

1/8/2013 0:00
1/8/2014 0:00
1/8/2015 0:00
1/9/2001 0:00
1/9/2002 0:00
1/9/2003 0:00
1 Solution

Accepted Solutions
MVP
MVP

Re: How to convert time string to date or time stamp?

May be this:

Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') as Date,

Date(Frac(Date#(myTime, 'M/D/YYYY h:mm'))) as Time,

Assuming your date 1/8/2013 means January 8th 2013 (and not August 8th 2013). If it is August 8th, then use this:

Date(Floor(Date#(myTime, 'D/M/YYYY h:mm')), 'MM/DD/YYYY') as Date,

Date(Frac(Date#(myTime, 'D/M/YYYY h:mm'))) as Time,

7 Replies
MVP
MVP

Re: How to convert time string to date or time stamp?

May be this:

Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') as Date,

Date(Frac(Date#(myTime, 'M/D/YYYY h:mm'))) as Time,

Assuming your date 1/8/2013 means January 8th 2013 (and not August 8th 2013). If it is August 8th, then use this:

Date(Floor(Date#(myTime, 'D/M/YYYY h:mm')), 'MM/DD/YYYY') as Date,

Date(Frac(Date#(myTime, 'D/M/YYYY h:mm'))) as Time,

datalakeyu
Valued Contributor

Re: How to convert time string to date or time stamp?

Hi Sunny , thanks so much for your help!

It works.

datalakeyu
Valued Contributor

Re: How to convert time string to date or time stamp?

Hi Sunny , thanks so much for your help!

One more question about this time issue.

1/8/2013 0:00 is January 8th 2013.

I did not use: Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY')  in the data load editor part.

I create a dimension (dimension name is: yearDimension): Year ( Date(Floor(Date#(myTime, 'M/D/YYYY h:mm')), 'MM/DD/YYYY') )

I get the following:

yearDimension:

2013

2014

2015

2001

2002

2003

And then I the above year (yearDimension) as a dimension for a time series chart.

The x-axis shows as 2013, 2014, 2015 , 2001 , 2002 , 2003. However, I would like put the year in order.

I tried the Qlik Sort (auto, numerical), but it does not work.

Could you please give any suggestion?

Is it possible to use Qlik custom Sort Expression? If so, could you please give an example?

Thanks again!

thi_pham
Contributor

Re: How to convert time string to date or time stamp?

For date dimension, I suggest you to view this thread Creating A Master Calendar

It's also better if you can build and manage a date dimension in your own data warehouse and just load to Qlik, you can research calendar date dimension with Kimball technique.

MVP
MVP

Re: How to convert time string to date or time stamp?

Sort numerically seems to be working

Capture.PNG

datalakeyu
Valued Contributor

Re: How to convert time string to date or time stamp?

Hi Sunny , thanks so much for your help!

I find it works when I change the Qlik Sorting part as:

1:Year (), check sort numerically

2: Sum(Sales)

If I changed as the following:

1: Sum(Sales)

2:Year (), check sort numerically

It will not work.

Hopefully this is helpful to other Qlik community members.

MVP
MVP

Re: How to convert time string to date or time stamp?

Yes, Year() needs to come first before Sum(Sales) for this to work