Date and Time Functions
MySQL's built-in date-related functions can be used in SELECT statements, with or
without specifying a table, to retrieve a result of the function. Or you can use the
functions with any type of date field.
There are in total 58 built-in date and time functions in SQL.
We must study 7.
YYYY-MM-DD
YYYY-MM-DD HH:MM:SS
1. NOW( ) – returns the current date and time as a value in the format – ‘YYYY-MM-DD
HH:MM:SS’ or ‘YYYYMMDD HHMMSS’ of the system (current time zone) at the time the
command was executed.
mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW() |
+---------------------------------------------------------+
| 2021-06-23 12:05:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
** This function does not need any argument value (neither a variable value nor a
column value).
2. DATE(date_expression_time) - returns the date part of the given date value (with
time) or of the date field otherwise current date.
mysql> SELECT DATE(‘2021-06-23 12:05:26’);
+---------------------------------------------------------+
| DATE(‘2021-06-23 12:05:26’) |
+---------------------------------------------------------+
| 2021-06-23 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE(NOW());
+---------------------------------------------------------+
| DATE(2023-10-09 10:03:56)
|
+---------------------------------------------------------+
| 2023-10-09 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE();
+---------------------------------------------------------------+
| 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 ')' at line 1 |
+---------------------------------------------------------------+
mysql> SELECT DATE(NOW());
+---------------------------------------------------------+
| DATE(NOW()) |
+---------------------------------------------------------+
| 2021-06-23 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE(NOW( ) )
1. NOW( ) = 2021-06-23 12:05:26
Now() returns - Current date + time
2. DATE(NOW( )) = DATE(2021-06-23 12:05:26) = 2021-06-23
Date( ) returns – date part of date_time expression
Consider the table – Frenzone
CREATE TABLE Frenzone (
Cod varchar,
Title VARCHAR,
DOF date
);
INSERT INTO Frenzone(Cod,Title,DOF)
VALUES
('Tan ','SONIC ','2010-12-25') ,
('Kewl ','Lava ','2018-06-11') ;
SELECT * FROM Frenzone;
Code ||Title ||DOF
Tan | SONIC | 2010-12-25
Kewl | Lava | 2018-06-11
mysql> SELECT DATE(DOF);
+---------------------------------------------------------+
| DATE(DOF) |
+---------------------------------------------------------+
| 2010-12-25 |
+---------------------------------------------------------+
| 2018-06-11 |
+---------------------------------------------------------+
2 rows in set (0.01 sec)
If the entry for the field named DOF was –
2010-12-25 12:20:36
2018-06-11 02:36:00
Code ||Title ||DOF
FF | SONIC | 2010-12-25 12:20:36
Fr | Lava | 2018-06-11 02:36:00
Then the output would be (the same)
mysql> SELECT DATE(DOF)FROM Frenzone;
+---------------------------------------------------------+
| DATE(DOF) |
+---------------------------------------------------------+
| 2010-12-25 |
+---------------------------------------------------------+
| 2018-06-11 |
+---------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> SELECT DATE(DOF)*2 AS “GEN FAR AHEAD” FROM Frenzone;
+---------------------------------------------------------+
| AS “GEN FAR AHEAD” |
+---------------------------------------------------------+
| 4020-12-25 |
+---------------------------------------------------------+
| 4036-06-11 |
+---------------------------------------------------------+
2 rows in set (0.01 sec)
Write a query to
i. extract date from the given date value – ‘2024-12-12 12:20:06’
ii. show 20 years later year from the current date of your system
iii. show 10 days after TODAY’S date
What will be the output of the statement –
i. mysql> SELECT NOW(DATE());
ii. mysql> SELECT DATE(NOW());
iii. mysql> SELECT DATE();
iv. mysql> SELECT DATE(‘2024-20-12’);
v. mysql> SELECT DATE(‘2024-02-30’);
vi. mysql> SELECT DATE(‘2024-12-2412:40:00’);
vii. mysql> SELECT DATE(‘2024-12-24 12:40:64’);
viii. mysql> SELECT DATE(NOW);
ix. mysql> SELECT NOW(DATE());
Extract date from the given date value – ‘2021-12-12 12:20:06’
mysql> SELECT DATE(‘2024-12-12 12:20:06’);
What will be the output of the statement –
i. mysql> SELECT NOW(DATE());
Error
ii. mysql> SELECT DATE(NOW());
DATE(NOW())
NOW() = 2024-06-23 12:44:06
DATE(NOW()) = DATE(2024-06-23 12:44:06)
2024-06-23
iii. mysql> SELECT DATE();
2024-06-23
iv. mysql> SELECT DATE(‘2024-20-12’);
ERROR because month value = 20 which is invalid (months are 1 to
12)
v. mysql> SELECT DATE(‘2024-02-30’);
ERROR because February month has no day as 30)
vi. mysql> SELECT DATE(‘2024-12-2412:40:00’);
ERROR because no space between 24 and 12 so invalid day value and
invalid time frame also.
vii. mysql> SELECT DATE(‘2024-12-24 12:40:64’);
ERROR seconds value invalid
viii. mysql> SELECT DATE(NOW);
ERROR NOW( ) is used without ( )
ix. mysql> SELECT NOW(DATE());
ERROR NOW( ) does not require an input argument.
*************
