Jump to content


Photo

**SOLVED** Date stored in 3 fields


  • Please log in to reply
3 replies to this topic

#1 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 30 August 2006 - 05:31 PM

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

#2 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 30 August 2006 - 05:45 PM

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...-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.
Legend has it that reading the manual never killed anyone.
My site

#3 sasa

sasa
  • Staff Alumni
  • Advanced Member
  • 2,804 posts
  • LocationHrvatska

Posted 30 August 2006 - 06:02 PM

try
select year*10000+month*100+day as date where date > 20060601


#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 30 August 2006 - 06:42 PM

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

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

$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>";
      }
?>

Works for me

Ray




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users