?
Solved

select date in order (newest first) from mysql db using asp - VARCHAR!

Posted on 2005-03-04
11
Medium Priority
?
1,154 Views
Last Modified: 2012-08-13
How do i sort a HTML table by date (newest date first).

This code below sorts oldest date first:

Set rs=con.execute("select * from trip_master where  agencyid = '"&agencyid&"' and newdate > '" &new_date& "' and regular_trip <> 'Cancelled' order by date_of_booking desc limit 50")

but

Set rs=con.execute("select * from trip_master where  agencyid = '"&agencyid&"' and newdate > '" &new_date& "' and regular_trip <> 'Cancelled' order by date_of_booking asc limit 50")  

doesnt dort newest first. How do i get around this? Unfortunately it is in the DB as a varchar in format 'DD/MM/YY'

Picco
0
Comment
Question by:crmpicco
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 13457694
the fact that it is a varchar and not a data data type is abit of an issue as the sort wont be accurate at all - is changing the datatype an option?
0
 
LVL 8

Expert Comment

by:fozylet
ID: 13457942
Also, for the date to make any sense while being sorted as a string, it should be in YYYYMMDD format...
0
 

Author Comment

by:crmpicco
ID: 13458267
is there a way i can sort it by the newest recordset?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:crmpicco
ID: 13458331
what i mean by that, can i sort it by the record that was last entered into the database (the newest one). There is no column in the DB for this, but can MySQL know what the last entered record was? i realise the problem is the fact that it is in as a Varchar, but this cannot be changed now.

 I must find a way that can sort a varchar in the format 'DD/MM/YY' and sort it chronologically - newest first.

This code:

Set rs=con.execute("select * from trip_master where  agencyid = '"&agencyid&"' and newdate > '" &new_date& "' and regular_trip <> 'Cancelled' order by date_of_booking asc limit 50")

gives me this in my ASP page:

Booking Date
04/02/05
11/02/05
11/02/05

I am looking for this:

Booking Date
11/02/05
11/02/05
04/02/05

and this code doesnt work:

Set rs=con.execute("select * from trip_master where  agencyid = '"&agencyid&"' and newdate > '" &new_date& "' and regular_trip <> 'Cancelled' order by date_of_booking desc limit 50")

Help appreciated.

Picco

0
 

Author Comment

by:crmpicco
ID: 13458335
obviousl it would have to take into consideration the MONTH as well as the date
0
 

Author Comment

by:crmpicco
ID: 13458927
any ideas?
0
 

Author Comment

by:crmpicco
ID: 13458972
i now have a auto_increment primary key in the table. and i put this into the sql hit - doesnt work...
0
 

Author Comment

by:crmpicco
ID: 13459016
This code:
select * from trip_master where agencyid = 'mk' and newdate > '20050304' and regular_trip <> 'Cancelled' order by pri_key limit 50

Gives me it OLDEST date at the top down to the NEWEST, eg...

Booking Date
18/02/05
25/02/05
28/02/05

How do i get it in reverse order to that????

Help.........................
0
 
LVL 8

Accepted Solution

by:
fozylet earned 310 total points
ID: 13459467
order by pri_key DESC limit 50
0
 
LVL 2

Expert Comment

by:vermon
ID: 13473639
How about populating an array - then use format date to actually convert your Varchar date to a proper date format
then query the array and sort your data?

If you need help with arrays and following thru let me know and I will post something.

0
 

Author Comment

by:crmpicco
ID: 13564306
How about populating an array - then use format date to actually convert your Varchar date to a proper date format
then query the array and sort your data?

If you need help with arrays and following thru let me know and I will post something.

---

how do i do this?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question