Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1157
  • Last Modified:

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
0
crmpicco
Asked:
crmpicco
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now