Help converting string to date

[Pal]

Hi

 

I wish to convert 3 individual column values - day, month and year into a string that can be read as a date and then ordered as a date?

 

Thanks!

[squiblo]

You mean something like this?

 

<?php
$query = mysql_query("SELECT * FROM date_table");

while ($row = mysql_fetch_assoc($query))
{
$day = $row['day'];
$month = $row['month'];
            $year = $row['year'];
}

echo $day ."/". $month ."/". $year ."<br />".;
?>

[PFMaBiSmAd]

Untested, but should work -

 

ORDER BY CONCAT_WS('-',year,month,day)

 

[Pal]

thanks,

 

once it is concatenated, will SQL read it and be able to order it as a date and not read it as text?

[PFMaBiSmAd]

It depends on exactly what format is in month and day. The code I posted assumes leading zero's so that the resulting format is yyyy-mm-dd, which is what is necessary to order dates correctly.

[Pal]

ah ok, unfortunately there aren't zeros so jan to sep appear as 1 - 9 and the same for days... Is there anyway of altering this in the same query?

[Pal]

By the way I ran it anyway despite the missing zeros, and it appears to have worked.

I queried the minimum of a group of dates for each row and it has produced results, which I am assuming means it was read correctly?

[PFMaBiSmAd]

You can force it into a DATE format by applying almost any function that returns a DATE value (tested for a date like 2008-1-9) -

 

ORDER BY DATE(CONCAT_WS('-',year,month,day))

[Pal]

Thanks for the help!