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: 
Anonymous
Not applicable

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
sunny_talwar

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,

View solution in original post

7 Replies
sunny_talwar

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,

Anonymous
Not applicable
Author

Hi Sunny , thanks so much for your help!

It works.

Anonymous
Not applicable
Author

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

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.

sunny_talwar

Sort numerically seems to be working

Capture.PNG

Anonymous
Not applicable
Author

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.

sunny_talwar

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