**SOLVED** Date stored in 3 fields

[craygo]

I have a table(not mine) that I have to work with. The date is stored in 3 seperate fields year, month, day.

How would I go about writing an sql statement which would put these fields together in the proper format and select dates that are in a certain range. I can do this without a problem using php after the query, but I would have to select every row in the table then filter out the results with php. I would like to select the rows first and just output it. The query and the time to display would be shorter.

Thanks

Ray

[AndyB]

Heh!  I've told the table owner to fix the database to use real dates and we all agree that would solve a zillion problems, but ...

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I suspect that CONCAT with a defined separator might point you in the right direction to generate a true date at query time.  Can't give you an exact query but that ought to help.

[sasa]

try[code]
select year*10000+month*100+day as date where date > 20060601[/code]

[craygo]

Thanks Andy!!  this is what I used and it works fine

$startd and $endd or my date parameters which are unix timestamps

[code]$sql = "SELECT UNIX_TIMESTAMP(CONCAT(`year`,'-',`month`,'-',`day`)) as mdate, string1 FROM testing WHERE UNIX_TIMESTAMP(CONCAT(`year`,'-',`month`,'-',`day`)) BETWEEN '$startd' AND '$endd'";
  $res = mysql_query($sql) or die (mysql_error());
    while($r = mysql_fetch_array($res)){
    echo "<table><tr><td>".$r['string1']." ".$r['mdate']."</td></tr></table><hr>";
      }
?>[/code]

Works for me

Ray