MySQL Date & time functions

---

MySQL Date & time functions

This tutorial,I have created table called orders2 in my_database as bellow.

CREATE TABLE IF NOT EXISTS `orders2` (   `ID` int(30) NOT NULL AUTO_INCREMENT,   `Date` date NOT NULL,   `Item` varchar(40) NOT NULL,   PRIMARY KEY (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; -- -- Dumping data for table `orders2` -- INSERT INTO `orders2` (`ID`, `Date`, `Item`) VALUES (1, '2008-07-15', 'Mobile phones'), (2, '2008-08-25', 'Calculators'), (3, '2008-09-20', 'Hand Bags'), (4, '2007-08-30', 'Clocks'), (5, '2007-07-28', 'Pensils'), (6, '2012-07-09', 'Books'), (7, '2012-07-08', 'Frocks');

Above table has only 2 rows.But we could get additional information using following functions.

DAYNAME()

YEAR()

MONTHNAME()

CURTIME()

CURDATE()

DATEDIFF()

WEEKOFYEAR()

WEEKDAY()

Create following example& you could get additional information as bellow.

<?php echo "<center><table bgcolor=#0b2d04> <tr bgcolor=#327423> <th><font color=#ffffff>ID</font></th> <th><font color=#ffffff>Date</font></th> <th><font color=#ffffff>Item</font></th> <th><font color=#ffffff>Month</font></th> <th><font color=#ffffff>Year</font></th> <th><font color=#ffffff>Day</font></th> <th><font color=#ffffff>Date Today</font></th> <th><font color=#ffffff>Time Today</font></th> <th><font color=#ffffff>Date Difference</font></th> <th><font color=#ffffff>Weeks</font></th> <th><font color=#ffffff>Days</font></th> </tr>"; $username="root"; $password=""; $database="my_database"; mysql_connect(localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $query ="(SELECT ID,Date,Item,MONTHNAME(Date),YEAR(Date),DAYNAME(Date),CURDATE(), CURTIME(),DATEDIFF(CURDATE(),Date),WEEKOFYEAR(Date),WEEKDAY(Date) FROM orders2)"; $result = mysql_query($query); while ($row = mysql_fetch_assoc($result)){ echo "<tr  bgcolor=#d7eecb> <td>" .$row['ID']."</td> <td>" .$row['Date']."</td> <td>" .$row['Item']."</td> <td>" .$row['MONTHNAME(Date)']."</td> <td>" .$row['YEAR(Date)']."</td> <td>" .$row['DAYNAME(Date)']."</td> <td>" .$row['CURDATE()']."</td> <td>" .$row['CURTIME()']."</td> <td>" .$row['DATEDIFF(CURDATE(),Date)']."</td> <td>" .$row['WEEKOFYEAR(Date)']."</td> <td>" .$row['WEEKDAY(Date)']."</td> </tr>"; } echo "</table></center>"; ?>

ID Date Item Month Year Day Date Today Time Today Date Difference Weeks Days 1 2008-07-15 Mobile phones July 2008 Tuesday 2012-07-09 23:56:41 1455 29 1 2 2008-08-25 Calculators August 2008 Monday 2012-07-09 23:56:41 1414 35 0 3 2008-09-20 Hand Bags September 2008 Saturday 2012-07-09 23:56:41 1388 38 5 4 2007-08-30 Clocks August 2007 Thursday 2012-07-09 23:56:41 1775 35 3 5 2007-07-28 Pensils July 2007 Saturday 2012-07-09 23:56:41 1808 30 5 6 2012-07-09 Books July 2012 Monday 2012-07-09 23:56:41 0 28 0 7 2012-07-08 Frocks July 2012 Sunday 2012-07-09 23:56:41 1 27 6