Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
stjernvd
Partner - Creator
Partner - Creator

List dates in order


Hi

When I create this table, my dates are not in order...

Dates:

LOAD
Concat(Chr(39) & Date(Floor(date))& Chr(39), ', ' ) As date;
SQL SELECT
date
FROM xxx;

They come out like this '01/09/2015', '02/09/2015', 11/08/2015', and so on...

They dates should be in correct order, how do I fix that?

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;

Load

     Date(Floor(TimeStamp#(dt_bus,'DD/MM/YYYY hh:mm:ss')),'YYYY-MM-DD') as dt_bus;


SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

View solution in original post

10 Replies
MK_QSL
MVP
MVP

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

PrashantSangle

Hi,

Use Order By

try like

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST

order By dt_bus desc;

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunilkumarqv
Specialist II
Specialist II

Dates:

LOAD
Concat(Chr(39) & dual(Date(Floor(dt_bus))& Chr(39), ', ' ),dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST order by dt_bus;

stjernvd
Partner - Creator
Partner - Creator
Author

Hi Manish,

THank you very much this worked!

Just one last question, how do I get the date to be in the format 'yyyy-mm-dd' since it is currently in dd-mm-yyyy?

MK_QSL
MVP
MVP

Dates:

LOAD
Concat(Chr(39) & Date(Floor(dt_bus))& Chr(39), ', ' ,dt_bus) As dt_bus;

Load

     Date(Date#(dt_bus,'DD-MM-YYYY'),'YYYY-MM-DD') as dt_bus;


SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

stjernvd
Partner - Creator
Partner - Creator
Author


Hi Manish,

I did this:

 

Dates:

LOAD
Concat(Chr(39) & Date(Date#(dt_bus,'DD-MM-YYYY'),'YYYY-MM-DD')&Chr(39), ', ' ,dt_bus) As dt_bus;
SQL SELECT
dt_bus
FROM TOOLS_DATA_HIST;

and it gives me this in my listbox: '', '', '', '',  etc...

what did I do wrong?

MK_QSL
MVP
MVP

Check my edited reply again..

stjernvd
Partner - Creator
Partner - Creator
Author

same thing happened:(

is it because dt_bus had a time stamp associated with it?

So the original format was 10/08/2015 00:00:00....

Does that change how I load the statement?

PrashantSangle

Hi,

I dont think so you required date#()

try without date#().

date(dt_bus,'YYYY-MM-DD')

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂