MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL TIMESTAMPDIFF in MySQL MySQL Syntax Checker Sudo MySQL Secure Installation

MySQL DATE_FORMAT() function
2023-01-02 17:03:38

MySQL DATE_FORMAT() function

In this context, we will learn how we can use the MySQL DATE_FORMAT() function with proper syntax and good examples.

Introduction of MySQL DATE_FORMAT() function

DATE_FORMAT() function in MySQL is used to format a specified date as a given format value, i.e., a date will be given, and this function will format that date as specified format parameters.

Syntax of the MySQL DATE_FORMAT() function

The syntax of the MySQL DATE_FORMAT() function is given as follows:

DATE_FORMAT(dt, Sformat)

Parameters or arguments used in MySQL DATE_FORMAT() function:

There are two parameters accepted by the DATE_FORMAT() function in MySQL, which are given as follows:

Dt: it is the mentioned date to be formatted.

Sformat: it is the mentioned format. This list of formats used in this function are listed below:

Format Description of MySQL DATE_FORMAT function:

%a: This abbreviation means weekday name. Its limit is from Sun to Sat.

%b: This abbreviation means month name. Its limit is from Jan to Dec.

%c: This abbreviation means numeric month name. Its limit is from 0 to 12.

%D: This abbreviation means a day of the month as a numeric value, followed by a suffix like 1st, 2nd, etc.

%e: This abbreviation means a day of the month as a numeric value. Its limit is from 0 to 31.

%f: This abbreviation means microseconds. Its limit is from 000000 to 999999.

%H: This abbreviation means hour. Its limit is from 00 to 23.

%I: This abbreviation means minutes. Its limit is from 00 to 59.

%j: This abbreviation means the day of the year. Its limit is from 001 to 366.

%M: This abbreviation means month name from January to December.

%p: This abbreviation means AM or PM.

%S: This abbreviation means seconds. Its limit is from 00 to 59.

%U: This abbreviation means week, where Sunday is the first day of the week. Its limit is from 00 to 53.

%W: This abbreviation means weekday names from Sunday to Saturday.

%Y: This abbreviation means year as a numeric value of 4 digits.

Returns :

It will return the formatted date.

Example 1:

In this example, we will get a formatted year as “2020” from the specified date “2020-11-23” in MySQL.

SELECT DATE_FORMAT("2020-11-23", "%Y");

Output:

2020
1 row in set (0.00 sec)

Example 2:

In this example, we will get a formatted month name as “December” from the specified date “2020-12-23” in MySQL.

SELECT DATE_FORMAT("2020-12-23", "%M");

Output:

December
1 row in set (0.00 sec)

Example 3:

In this example, we will get a day of the month as a numeric value as “23rd” from the specified date “2020-11-23” in MySQL.

SELECT DATE_FORMAT("2020-11-23", "%D");

Output:

23rd
1 row in set (0.00 sec)

Example-4 :

In this example, we will get the month, day, and year as “December 23, 2020” from the specified date “2020-12-23” in MySQL.

SELECT DATE_FORMAT("2020-12-23", "%M %d %Y");

Output:

December 23 2020
1 row in set (0.00 sec)

Example 5:

In this example, we will get hour and minute as “12 09” from the specified date and time “2020-11-23 12:09:23”.

SELECT DATE_FORMAT("2020-11-23 12:09:23", "%H %i");

Output:

12 09
1 row in set (0.00 sec)

Application of MySQL DATE_FORMAT() function:

This function is used to format a specified date as a given format value, i.e., a date will be given, and this function will format it.

Summary:

In the above context, we have learned how to use the DATE_FORMAT() function in MySQL to format a specified date as a given format value, i.e., a date will be given, and this function will format it.