MySQL is derived from ‘My’- the cofounder’s daughter and SQL- Structured Query Language. It is an open-source type of relational database management system. This relational database system helps organize data into relatable tables, thereby enabling structural relations of different data sets.
MySQL date is one of the temporal data types that can manage data values alongside other data types such as numeric data types, String data types, Boolean data types, date and time, and spatial data types.
The DATE_FORMAT() function is used in MySQL to format date using the specified format value. For instance, if a date is provided, the function will format it concerning the specified parameters.
MySQL date format syntax
DATE_FORMAT(date, format)
The syntax function above accepts two parameters as shown below:
- date – It specifies the date to be formatted
- format – It defines the format to be used to format the date
Below is a list of formats that you should familiarize yourself with when dealing with the date function.
- %a – this format displays the weekday name. it is limited from Sunday to Saturday.
- %b – this format symbol shows the month name. it is restricted from January to December.
- %c – this format symbol indicates the numeric month name. it is limited from 0 to 12
- %D – this format symbol shows the numeric value of the day of the month followed by a suffix such as 1st or 2nd.
- %e – this format symbol shows the numeric value of the day of the month. it is limited from 0 to 31
- %f – this format symbol shows microseconds. it is limited from 000000 to 999999.
- %H – this format symbol indicates the hour. It is limited from 00 to 23.
- %i – this format symbol shows the minutes. it is limited from 00 to 59.
- %j – this format symbol shows the day of the year. it is limited from 001 to 366.
- %M – this format symbol indicates the month name. it is limited from January to December
- %p – this format symbol show PM or AM
- %S – this format symbol shows seconds. it is limited from 00 to 59
- %U – this format symbol shows weekdays where Sunday is the first day. It is limited from 00 to 53.
- %W – this format symbol shows the weekdays from Sunday to Saturday
- %Y – this format symbol shows the year’s numeric value as a 4-digit number
The formats listed above return a formatted date.
Example 1:
To extract or show the year from the current date, we shall execute the following command line:
SELECT DATE_FORMAT("2021-09-25", "%Y");
Output:
MySQL show year
Example 2:
Extract month name from a given date as shown below:
2021-09-25
SELECT DATE_FORMAT("2021-09-25", "%M");
Output:
MySQL show month name
Example 3:
Extract hours and minutes from the date provided below:
2021-09-25
SELECT DATE_FORMAT("2021-09-25 10:20:23", "%H %i");
Output:
MySQL show hours and minutes, respectively
Example 4:
Extract the day of the month numerically from the date specified below:
2021-09-25
SELECT DATE_FORMAT("2021-09-25", "%D");
Output:
MySQL Show day of the month numerically
Example 5:
Extract the month, day, and year respectively from the date provided herein:
2021-09-25
SELECT DATE_FORMAT("2021-09-25", "%M %d %Y");
Output:
MySQL show month, day, and a year respectively
MySQL date uses the format; yyyy-mm-dd to store a data value, and this format is usually fixed with no permission to change it whatsoever. For the storage requirements of MySQL, to store a single date value, it would use about 3 bytes and has a date value ranging from 1000-01-01 to 9999-12-31. Therefore, a user who needs to store a date range out of these parameters only be possible through integers. However, this process is quite taxing, and it would require you to create three columns, one for the year, another for the month, and the last one for the day.
This is often not recommended to try on most occasions if you want to change the date format. Disabling the ‘strict mode’ will help convert any invalid date to the ‘zero date value.’ An invalid date in this instance would not follow the standard MySQL date format, for example, 2020-01-24. This would also create a need to develop ‘stored functions,’ which would reproduce MySQL’s newly created date functions.
MySQL date values with 2-digit years
MySQL will only accept two-digit year values under the following conditions:
- Year values between 00-69 are converted to 2000-2069
- Year values between 70-79 are converted to 1970-1999
It is, however, advisable to evade using date values with two digits as it is ambiguous.
Here is an example. A table is created with the name ‘people’ and has ‘data types’ columns:
CREATE TABLE IF NOT EXISTS people ( JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', MIN_SALARY decimal(6,0) DEFAULT 8000, MAX_SALARY decimal(6,0) DEFAULT NULL )ENGINE=InnoDB;
Create table
The following function will ‘insert a row’ into the ‘people’ table:
INSERT INTO people(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES('5', 'Foss', '2000', '2000');
Insert Rows into tables
Now ‘Query the data’ from the ‘people’ table
SELECT * FROM people;
Query Table
After running the commands above, it will now be possible to use the 2-digit year format option to insert data to the ‘people’ table:
INSERT INTO people(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY,BIRTH)
VALUES('6', 'FOSSLINUX', '200', '2000', '01-09-01');
‘01’ has been used in the first row as the year, falling between the (range 00-69). MySQL will then convert it to 2001, while in the second row, the use of ‘84’ falls in the range (70-99), and MySQL converts it to 1984.
MySQL date functions
MySQL often provides and uses several date functions, which helps users manipulate their data more effectively and quickly.
For instance, using the ‘NOW( )’ function helps to fetch the current date and time:
SELECT NOW( );
Select now
To get only the date part of a ‘DATETIME,’ use only the ‘DATE( )’ function:
SELECT DATE(NOW( ) );
Select date now
To get the current system date, use CURDATE ( ):
SELECT CURDATE();
Current Date
The ‘DATE_FORMAT’ function can be used to format the date value to this format; mm/dd/yyyy. It uses the date format pattern of %m/%d/%Y
SELECT DATE_FORMAT('2021-09-25 22:23:00', '%W %D %M %Y');
Date format mdy
If you want to calculate the number of days between two date values, use the ‘DATEDIFF’ function:
mysql> SELECT DATEDIFF('2020-09-25', '2021-09-25') days;
Date Difference
Using the ‘DATE_ADD’ function helps one to add several years, months, weeks, or days:
SELECT DATE_ADD('2021-09-25', INTERVAL 31 DAY);
Date Add
The ‘DATE_SUB’ function can also be used to subtract a date interval:
SELECT DATE_SUB("2021-09-25", INTERVAL 10 DAY);
Date Subtract
Using the corresponding function in terms of the date, month, Quarter and Year will help you to get the exact day, month, etcetera for a date value as shown below:
SELECT DAY(‘2000-12-31’) day,
MONTH( ‘2000-12-31’) month
QUARTER(‘2000-12-31’) quarter,
YEAR(‘2000-12-31’) year
Day ǀ month ǀ quarter ǀ year ǀ
31 ǀ 12 ǀ 4 ǀ 2000ǀ
Weekly information of related functions can be accessed through:
SELECT
WEEKDAY(‘2000-12-31’) weekday,
WEEK( ‘2000-12-31’) week,
WEEKOFTHEYEAR(‘2000-12-31’) weekofyear;
weekday ǀ week ǀ weekofyear ǀ
6 ǀ 53 ǀ 52 ǀ
The week function always returns a week number having a zero-based index. You will need to pass a second argument or pass a ‘0’ to prevent this from happening. Passing ‘1’ will return the week number ‘1-indexed’.
WEEKDAY(‘2000-12-31’) weekday,
WEEK( ‘2000-12-31’, 1) week,
WEEKOFTHEYEAR(‘2000-12-31’) weekofyear;
weekday ǀ week ǀ weekofyear ǀ
6 ǀ 52 ǀ 52 ǀ
Conclusion
This is a short article tutorial that covers the subject MySQL date format. We hope all the aspects you wanted to get versed with on MySQL date formats have been covered. Remember to give a thumbs up via the comments section if you find the article helpful.
