[SOLVED] mysql lowest of 2 dates

[whitedragon101]

Need to get the lowest of 2 dates.

 

I am trying this syntax but it does not work.  I am sure there is some simple change I need to make it work.

Any ideas?

 

SELECT MIN('2012-10-09','2011-10-01');

 

 

thanks

[Maq]

Assuming they're 'DATE' types, you can use:

 

ORDER BY date_field ASC LIMIT 2

[akitchin]

are you getting an error? if so, what is it? is that the exact string you're passing to MySQL? if so, you should remove the semicolon.

[whitedragon101]

The error is

 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '2011-10-01')' at line 1

 

 

[whitedragon101]

This is the string I am passing into the MySQL console so need a semicolon to terminate the string.  The following is an exact copy of a line I used in the console with DATEDIFF, which does return the difference between the two dates.

 

mysql > SELECT DATEDIFF('2012-10-09','2011-10-01');

works

 

However the query I need now is to get the lowest of the two dates and am again testing it in the console and:

 

mysql > SELECT MIN('2012-10-09','2011-10-01');

returns the error listed above.

[Maq]

MIN() is an aggregate function that takes a field, not a value.  Try:

 

SELECT date_field FROM table ORDER BY date_field ASC LIMIT 2

[whitedragon101]

What I have is a table structured thus:

 

Job ID | Job Name | StartDate1 | EndDate1 | StartDate2 | EndDate2 | StartDate3 | EndDate3

 

I have been trying to get the earliest of the start dates (and after that I will need the latest of the end dates).

I want to display a table the just has the first two fields then the earliest and latest dates for each row like this:

 

Job ID | Job Name | Earliest Date | Latest Date

 

This is why I am having trouble finding a solution because it is for the ealiest and latest dates for each ROW not COLUMN.

 

I have been trying to do it like this.

($row_job_set is a row of a simple SELECT * query from the table described above ).

 

  <table border="1">
        <tr>
          <td>Job Id</td>
          <td>Job Name</td>
          <td>Earliest Date</td>
          <td>Latest Date</td>
        </tr>
        <?php do { 

                $startDate1 = $row_job_set['startDate1'];
                $startDate2 = $row_job_set['startDate2'];
                $startDate3 = $row_job_set['startDate3'];
                $lowestDateQuery = "SELECT MIN('$startDate1','$startDate2','$startDate3')";
        $lowestDate = mysql_query($lowestDateQuery);
	$dRow = mysql_fetch_row($lowestDate);
	$dResult = mysql_result($dRow,0);
	$totalRows_lowestDate= mysql_num_rows($lowestDate);
	?>
          <tr>
            <td><?php echo $row_job_set['job_id']; ?></td>
            <td><?php echo $row_job_set['job_name']; ?></td>
            <td><?php echo $totalRows_lowestDate  ?> </td>
            <td>Not yet implimented</td>
          </tr>
          <?php } while ($row_job_set = mysql_fetch_assoc($job_set)); ?>
      </table>

 

[akitchin]

... why would you use MySQL for this instead of PHP? the data's already in PHP. my suggestion would be to select a UNIX_TIMESTAMP()d version of the dates to compare, and then use PHP's min function.

[whitedragon101]

 

Thanks guys

 

Have used the strtotime() to convert the date to a unix timestamp.

 

Then used the php min() function to find the lowest date.

 

The used the gmdate("Y-m-d ", $lowestDate) function to convert the lowest date into a printable date again.