SQL Working With Dates
MySQL comes with the following data types for storing a date or a date/time
value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
SQL Working with Dates
Look at the following table:
Orders Table
OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
The result-set will look like this:
OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11
Now, assume that the "Orders" table looks like this (notice the added time-
component in the "OrderDate" column):
OrderId ProductName OrderDate
1 Geitost 2008-11-11 13:23:4
2 Camembert Pierrot 2008-11-09 15:45:2
3 Mozzarella di Giovanni 2008-11-11 11:12:0
4 Mascarpone Fabioli 2008-10-29 14:56:5
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with
no time portion.
SQL | Date functions
NOW(): Returns the current date and time. Example:
SELECT NOW();
Output:
2017-01-13 08:03:52
CURDATE(): Returns the current date. Example:
SELECT CURDATE();
Output:
2017-01-13
CURTIME(): Returns the current time. Example:
SELECT CURTIME();
Output:
08:05:15
DATE(): Extracts the date part of a date or date/time expression. Example:
For the below table named ‘Test’
Id Name BirthTime
4120 Pratik 1996-09-26 16:44:15.581
SELECT Name, DATE(BirthTime) AS BirthDate FROM Test;
Output:
Name BirthDate
Prati
k 1996-09-26
EXTRACT(): Returns a single part of a date/time.
SELECT Name, Extract(DAY FROM BirthTime) AS BirthDay
FROM Test;
Output:
Name BirthDay
Pratik 26
SELECT Name, Extract(YEAR FROM BirthTime) AS BirthYear
FROM Test;
Output:
Name BirthYear
Pratik 1996
DATE_ADD() : Adds a specified time interval to a date
Syntax:
DATE_ADD(date, INTERVAL expr type);
Where, date – valid date expression and expr is the number of interval we
want to add.
and type can be one of the following:
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH,
QUARTER, YEAR, etc.
Example:
For the below table named ‘Test’
Id Name BirthTime
4120 Pratik 1996-09-26 16:44:15.581
Queries
SELECT Name, DATE_ADD(BirthTime, INTERVAL 1 YEAR) AS
BirthTimeModified FROM Test;
Output:
Name BirthTimeModified
Prati
k 1997-09-26 16:44:15.581
SELECT Name, DATE_ADD(BirthTime, INTERVAL 30 DAY) AS
BirthDayModified FROM Test;
Output:
Name BirthDayModified
Prati
k 1996-10-26 16:44:15.581
SELECT Name, DATE_ADD(BirthTime, INTERVAL 4 HOUR) AS
BirthHourModified FROM Test;
Output:
Name BirthSecond
Prati
k 1996-10-26 20:44:15.581
DATE_SUB(): Subtracts a specified time interval from a date.
Syntax for DATE_SUB is same as DATE_ADD just the difference is that
DATE_SUB is used to subtract a given interval of date.
DATEDIFF(): Returns the number of days between two
dates.Syntax:
DATEDIFF(date1, date2);
date1 & date2- date/time expression
Example:
SELECT DATEDIFF('2017-01-13','2017-01-03') AS DateDiff;
