create table date_table(name varchar(50),birthdate datetime);
insert into date_table(name,birthdate) values('sangram','1981-04-26 21:40:00');
insert into date_table(name,birthdate) values('sagar','1971-12-12 10:20:00');
insert into date_table(name,birthdate) values('sachin','1984-09-21 05:20:00');
insert into date_table(name,birthdate) values('swapnil','2003-05-18 07:10:00');
select * from date_table;
output:
+---------+---------------------+
| name | birthdate |
+---------+---------------------+
| sangram | 1981-04-26 21:40:00 |
| sagar | 1971-12-12 10:20:00 |
| sachin | 1984-09-21 05:20:00 |
| swapnil | 2003-05-18 07:10:00 |
+---------+---------------------+
-- DATE_FORMAT:
select name,DATE_FORMAT(birthdate, '%d-%m-%Y %H:%i:%s') from date_table;
Output:
+---------+---------------------------------------------+
| name | DATE_FORMAT(birthdate, '%d-%m-%Y %H:%i:%s') |
+---------+---------------------------------------------+
| sangram | 26-04-1981 21:40:00 |
| sagar | 12-12-1971 10:20:00 |
| sachin | 21-09-1984 05:20:00 |
| swapnil | 18-05-2003 07:10:00 |
+---------+---------------------------------------------+
-- DATE ADD
SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
Output:
+---------------------------------------+
| DATE_ADD('2018-05-01',INTERVAL 1 DAY) |
+---------------------------------------+
| 2018-05-02 |
+---------------------------------------+
same as ADDDATE
-- DATE SUBSTRACT
SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
Output:
+----------------------------------------+
| DATE_SUB('2018-05-01',INTERVAL 1 YEAR) |
+----------------------------------------+
| 2017-05-01 |
+----------------------------------------+
same as SUBDATE
Here interval we specified is year & day other possible values can be
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_DIFF:
difference between two dates in days
SELECT DATEDIFF('2007-12-31','2007-12-30 23:59:59');
Output:
+----------------------------------------------+
| DATEDIFF('2007-12-31','2007-12-30 23:59:59') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
Only the date parts of the values are used in the calculation.Hence even if difference is of one second we get 1 day as result
-- Extract day,MONTH,year from date
select name,DAY(birthdate),month(birthdate),year(birthdate) from date_table;
Output:
+---------+----------------+------------------+-----------------+
| name | DAY(birthdate) | month(birthdate) | year(birthdate) |
+---------+----------------+------------------+-----------------+
| sangram | 26 | 4 | 1981 |
| sagar | 12 | 12 | 1971 |
| sachin | 21 | 9 | 1984 |
| swapnil | 18 | 5 | 2003 |
+---------+----------------+------------------+-----------------+
-- Extract:
SELECT EXTRACT(YEAR FROM birthdate),EXTRACT(MONTH FROM birthdate),EXTRACT(DAY FROM birthdate) from date_table;
output:
------------------------------+-------------------------------+-----------------------------+
| EXTRACT(YEAR FROM birthdate) | EXTRACT(MONTH FROM birthdate) | EXTRACT(DAY FROM birthdate) |
+------------------------------+-------------------------------+-----------------------------+
| 1981 | 4 | 26 |
| 1971 | 12 | 12 |
| 1984 | 9 | 21 |
| 2003 | 5 | 18 |
+------------------------------+-------------------------------+-----------------------------+
GET TIME part from date:
select TIME(birthdate) from date_table;
Output:
+-----------------+
| TIME(birthdate) |
+-----------------+
| 21:40:00 |
| 10:20:00 |
| 05:20:00 |
| 07:10:00 |
+-----------------+
-- STRING TO DATE:
STR_TO_DATE() returns a DATETIME value if the format string
contains both date and time parts, or a DATE or TIME value
if the string contains only date or time parts.
SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
Output:
+-------------------------------------+
| STR_TO_DATE('01,5,2013','%d,%m,%Y') |
+-------------------------------------+
| 2013-05-01 |
+-------------------------------------+
SELECT STR_TO_DATE('05,40,35','%H,%i,%S');
Output:
+------------------------------------+
| STR_TO_DATE('05,40,35','%H,%i,%S') |
+------------------------------------+
| 05:40:35 |
+------------------------------------+
SELECT STR_TO_DATE('01,5,2013,05,40,35','%d,%m,%Y,%H,%i,%S');
Output:
+-------------------------------------------------------+
| STR_TO_DATE('01,5,2013,05,40,35','%d,%m,%Y,%H,%i,%S') |
+-------------------------------------------------------+
| 2013-05-01 05:40:35 |
+-------------------------------------------------------+
-- Day Name
SELECT DAYNAME("2017-06-15");
output:
+-----------------------+
| DAYNAME("2017-06-15") |
+-----------------------+
| Thursday |
+-----------------------+
-- Week
This function returns the week number for date.
The two-argument form of WEEK() enables you
to specify whether the week starts on Sunday or Monday
Mode :
1- Monday
0- Sunday
SELECT WEEK('2009-01-08',1);
output:
+----------------------+
| WEEK('2009-01-08',1) |
+----------------------+
| 2 |
+----------------------+
--QUARTER
SELECT QUARTER('2008-04-01');
Output:
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
| 2 |
+-----------------------+
-- Date Diff in MINUTE:
select TIMESTAMPDIFF(MINUTE,'2023-04-25 23:59:00','2023-04-26')
Output:
+----------------------------------------------------------+
| TIMESTAMPDIFF(MINUTE,'2023-04-25 23:59:00','2023-04-26') |
+----------------------------------------------------------+
| 1 |
+----------------------------------------------------------+
first argument can be any of following
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
select TIMESTAMPDIFF(SECOND,'2023-04-25 23:59:00','2023-04-26');
Output:
+----------------------------------------------------------+
| TIMESTAMPDIFF(SECOND,'2023-04-25 23:59:00','2023-04-26') |
+----------------------------------------------------------+
| 60 |
+----------------------------------------------------------+
No comments:
Post a Comment