Select query for Difference in day in a date time field mysql table

SELECT DAY('2016-07-27');

Output is 27

Syntax

DAY(date)

Note that this will not take care of month and year, for example the difference in days between 12th July 04 and 14th July 05 is two days only.

DAYNAME(): Day Name in Full ( Monday )
DEMO: Select date from Calendar to execute DAY()
We will use one PHP Script to get the current date by using DAYNAME()

For easy understanding we will create one PHP page with this query with all other fields.

Yesterday , Today and Tomorrow

SELECT DAY(CURDATE()- interval 1 day)  as YesterDay, DAY(CURDATE()) as Today, DAY(CURDATE()+ interval 1 Day) as Tomorrow

YesterDay	Today	Tomorrow
11	12	13

PHP Script

To display the above details we used PHP Script, you can use the same script for using all above queries.

config.php connection string is available here

PDO for executing Queries in PHP

How to get records using FETCH() are here

<?Php
require 'config.php';  // Database connection string
$query='SELECT DAY(CURDATE()- interval 1 day)  as YesterDay, DAY(CURDATE()) as Today, DAY(CURDATE()+ interval 1 Day) as Tomorrow';
// Change the above Query part to get different results // 

$count=$dbo->prepare($query);
$count->execute();
$row = $count->fetch(PDO::FETCH_OBJ);
echo "
<table class='table table-striped'>
<tr class='info'><td>YesterDay</td><td>Today</td><td>Tomorrow</td></tr>
<tr><td>$row->YesterDay</td><td>$row->Today</td><td>$row->Tomorrow</td></tr>
</table>";

?>

The basic query for MySQL table is here

SELECT day(dt2) FROM `dt_tb`

This will collect the day part from the field

Difference in Day

The SQL query is modified to display all the columns with the days and their difference for easy comparison. Here is the query used.
Note that this is only math difference between two day ( numbers ) only and to get difference in dates we have to use TO_DAYS()

<?Php
require "config.php";// Database connection

$query="SELECT dt,dt2,day(dt) as day1,day(dt2)as day2,(day( dt2 ) - day( dt )) as difference FROM `dt_tb` ";

if($stmt = $connection->query("$query")){

  echo "No of records : ".$stmt->num_rows."<br>";

  echo "<table class='table table-striped'>
<tr class='info'> <th> dt</th><th> dt2</th><th>day1</th><th>day2</th><th>Difference</th></tr>";
while ($row = $stmt->fetch_assoc()) {
 echo "<tr><td>$row[dt]</td><td>$row[dt2]</td><td>$row[day1]</td><td>$row[day2] </td><td>$row[difference] </td></tr>";
 }
echo "</table>";
}else{
echo $connection->error;
}
?>

The output of this query is here

dt	dt2	day1	day2	difference
2004-10-26 00:00:00	2005-01-25	26	25	-1
2004-05-05 23:56:25	2005-06-12	5	12	7
2005-12-08 13:20:10	2005-06-06	8	6	-2

Here is the sql code to create and fill the table with records

CREATE TABLE IF NOT EXISTS `dt_tb` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dt2` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `dt_tb`
--

INSERT INTO `dt_tb` (`id`, `dt`, `dt2`) VALUES
(1, '2004-10-26 00:00:00', '2005-01-25'),
(2, '2004-05-05 23:56:25', '2005-06-12'),
(3, '2005-12-08 13:20:10', '2005-06-06');

SQL Date References MONTH() YEAR() NOW() : Current Date with Time

Subscribe to our YouTube Channel here

Naren	29-02-2012
How to select a record using particular date? What is the SQL query for that?

DAY() : Day of the Month as number

Yesterday , Today and Tomorrow

PHP Script

Difference in Day

Subscribe