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

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
crmpiccoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aprestoCommented:
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
fozyletCommented:
Also, for the date to make any sense while being sorted as a string, it should be in YYYYMMDD format...
0
crmpiccoAuthor Commented:
is there a way i can sort it by the newest recordset?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

crmpiccoAuthor Commented:
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
crmpiccoAuthor Commented:
obviousl it would have to take into consideration the MONTH as well as the date
0
crmpiccoAuthor Commented:
any ideas?
0
crmpiccoAuthor Commented:
i now have a auto_increment primary key in the table. and i put this into the sql hit - doesnt work...
0
crmpiccoAuthor Commented:
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
fozyletCommented:
order by pri_key DESC limit 50
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vermonCommented:
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
crmpiccoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.