Page 1 of 1

database query

Posted: Mon Apr 18, 2005 10:12 am
by gurjit
Hi all,

I have a varchar field which stores dates like "22/05/2004" UK format. When i asc or desc the order, the order i choose to display is not correct. How can i make the order display correct in the query.

This my query. I want the data to order by date using "Y-m-d" format from this field.

Code: Select all

<?php
select * from studentsexp,tbl_school,tbl_location where studentsexp.SCHOOL_CODE = tbl_school.sc_code and tbl_location.lid = tbl_school.frn_lid and  tbl_location.frn_cid = $frn_cid order by studentsexp.SCHOOL_CODE,studentsexp.DOB_DD_MM_ desc
?>

Posted: Mon Apr 18, 2005 10:19 am
by Calimero
If you are not using DATE or TIME types, why don't use 3 columns - one for Year, one for Month and one for Date.

Much easier to search and sort.

Then just sort them by those fields ASC or DESC - however do you like.

Posted: Mon Apr 18, 2005 11:02 am
by JayBird
classic case of using the wrong type of field to store your data....you really shouldn't be storing dates as strings, instead, store the timestamp...trust me, it will make you life a whole lot easier

Posted: Tue Apr 19, 2005 3:34 am
by gurjit
I always do store dates with data type date but i was given raw data in csv format woth DOB for students in UK date format.

I created another field called DOB and wrote a function to store the date in the correct format and then manipulated my data in the appropriate tables.

Thanks for your help people, i took your advise Calimero.