[RESOLVED] dates in ascending order

[Ali_baba]

Hello,

I am working on a database for medical purpose. There is a field to enter the patient's visit date.
The date format is dd/mm/yyyy. While viewing the records, the records must be in ascending order according to the dates.

I do know that SQL saves dates in YYYY-MM-DD. However, I am unable to think of any approach to save record from dd/mm/yyyy to yyyy-mm-dd, and to get the record back from yyyy-mm-dd to dd/mm/yyyy. Will the SQL query take care of records if I want to see them in ascending order by date?

Can somebody please help me here? Thanks in advance 🙂

[herve]

To order, you can use the ORDER BY SQL statement like this

SELECT id,name,date FROM patients ORDER BY visit_date;

(ascending order)

or

SELECT id,name,date FROM patients ORDER BY visit_date DESC;

(descending order)

Once you get the date from MySQL, let's say in "$visit_date" variable :

$visit_year  = substr ($visit_date,0,4);
$visit_month = substr ($visit_date,5,2);
$visit_day   = substr ($visit_date,8,2);

$my_date = "$visit_day/$visit_month/$visit_year";

print $my_date;

[Ali_baba]

Hello,

Thanks for your reply. Your solution did help me in putting the data from dd/mm/yyyy to yyyy-mm-dd.

However, since I am using mysql_fetch_array so how can I substr the incomming records? I am trying to display the list as well. like:

patient id:1
name: xxx
date_visit: 01/01/06

patient id:2
name: xxx
date_visit: 02/01/06

patient id:3
name: xxx
date_visit: 03/01/06

[bodzan]

//ONLY IF YOUR DATABASE DATE IS SET TO BE IN THIS FORMAT YYYY-MM-DD
$sql = mysql_query("SELECT * FROM $TABLE ORDER BY visit_date DESC");
while($result = mysql_fetch_array()){
     $visit_year = substr ($result['visit_date'],0,4);
     $visit_month = substr ($result['visit_date'],5,2);
     $visit_day    = substr ($result['visit_date'],8,2);

 $my_date = "$visit_day/$visit_month/$visit_year";

 print $my_date; 

}

[Ali_baba]

Thanks. my problem is solved 🙂