Jump to content

Archived

This topic is now archived and is closed to further replies.

craygo

**SOLVED** Date stored in 3 fields

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
try[code]
select year*10000+month*100+day as date where date > 20060601[/code]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.