MySQL DATE data type

MySQL DATE data type is very important data type. Knowledge of this data type and its corresponding function makes the life of developer easy. So every developer and DBA  must have the knowledge of this data type.

MySQL comes with several data types for storing dates in its database system: DATE, TIMESTAMP, DATETIME and YEAR.

MySQL uses yyyy-mm-dd format for storing a date value. This format is fixed and it is not possible to change it.

For example, you may want to use mm-dd-yyyy format for your application but you can’t. Instead, you follow the standard date format and use the DATE_FORMAT function to format the date the way you want.

When strict mode is disabled, MySQL converts any invalid date e.g., 2015-02-30 to the zero date value 0000-00-00.

For below example we have created a table called user. the scema is given below

CREATE TABLE people (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(50) NOT NULL,

    birth_date DATE NOT NULL

);

 

How to Insert a Date in MySQL using CURDATE

To insert the current date into your table you can use MySQL's built-in function CURDATE() in your query.

INSERT INTO user(name, birth_date) VALUE ('Testing', CURDATE() )";

Output

 select * from user;
+----+---------+------------+
| id | name    | birth_date |
+----+---------+------------+
|  1 | Testing | 2018-09-18 |
+----+---------+------------+

Formatting the Date

To format the date MySQL provides DATE_FORMAT() function

Query

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') date;

Output

+------------+
| date       |
+------------+
| 09/18/2018 |
+------------+
1 row in set (0.00 sec)

• %a-Abbreviated weekday name (Sun-Sat)
• %b-Abbreviated month name (Jan-Dec)
• %c-Month, numeric (0-12)
• %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
• %d-Day of month, numeric (00-31)
• %e-Day of month, numeric (0-31)
• %f-Microseconds (000000-999999)
• %H-Hour (00-23)
• %h-Hour (01-12)
• %I-Hour (01-12)
• %i-Minutes, numeric (00-59)
• %j-Day of year (001-366)
• %k-Hour (0-23)
• %l-Hour (1-12)
• %M-Month name (January-December)
• %m-Month, numeric (00-12)
• %p-AM or PM
• %r-Time, 12-hour (hh:mm:ss followed by AM or PM)
• %S-Seconds (00-59)
• %s-Seconds (00-59)
• %T-Time, 24-hour (hh:mm:ss)
• %U-Week (00-53) where Sunday is the first day of week
• %u-Week (00-53) where Monday is the first day of week
• %V-Week (01-53) where Sunday is the first day of week, used with %X
• %v-Week (01-53) where Monday is the first day of week, used with %x
• %W-Weekday name (Sunday-Saturday)
• %w-Day of the week (0=Sunday, 6=Saturday)
• %X-Year for the week where Sunday is the first day of week, four digits, used with %V
• %x-Year for the week where Monday is the first day of week, four digits, used with %v
• %Y-Year, numeric, four digits
• %y-Year, numeric, two digits

Query

 SELECT DATE_FORMAT(CURDATE(), '%m-%d-%Y') date;

Output  

+------------+
| date       |
+------------+
| 09-18-2018 |
+------------+
1 row in set (0.00 sec)

Query

SELECT DATE_FORMAT('2018-09-09', '%W %M %Y');

Output 

+---------------------------------------+
| DATE_FORMAT('2018-09-09', '%W %M %Y') |
+---------------------------------------+
| Sunday September 2018                 |
+---------------------------------------+
1 row in set (0.00 sec)

Calculating the days between two dates

 

MySQL provide DATEDIFF(cur_date,previus_date) function to calculate days b/w two days

Query

SELECT DATEDIFF('2018-09-23','2018-08-09') days;

Output  

+------+
| days |
+------+
|   45 |
+------+
1 row in set (0.00 sec)

Extracting the day, month, year and quarter from a given date

Query

SELECT DAY('2018-08-31') day, MONTH('2018-08-31') month, QUARTER('2018-08-31') quarter, YEAR('2018-08-31') year;

+------+-------+---------+------+
| day  | month | quarter | year |
+------+-------+---------+------+
|   31 |     8 |       3 | 2018 |
+------+-------+---------+------+
1 row in set (0.00 sec)

Extracting the last day from a given date

Query

SELECT LAST_DAY('2018-09-09') ;

Output  

+------------------------+
| LAST_DAY('2018-09-09') |
+------------------------+
| 2018-09-30             |
+------------------------+
1 row in set (0.00 sec)

Extracting the name of the day

Query

SELECT DAYNAME('2018-09-09');

Output

+-----------------------+
| DAYNAME('2018-09-09') |
+-----------------------+
| Sunday  &n |
+-----------------------+
1 row in set (0.00 sec)


References :

https://dev.mysql.com/doc/refman/5.7/en/