MICROSECOND() Function in MySQL

MySQL DATE_FORMAT() Function

Last Updated : 03 Jun, 2024

MySQL DATE_FORMAT() function formats a specified date to a given format value.

DATE_FORMAT() Function in MySQL

The DATE_FORMAT() function in MySQL formats a specified date or datetime according to the given format.

The DATE_FORMAT() function allows you to customize the display of date and time values in MySQL by using a combination of format specifiers.

t is commonly used in SELECT statements to format date columns for better readability and presentation.

Syntax

MySQL DATE_FORMAT function Syntax is:

DATE_FORMAT(date, format)

Parameters:

This function accepts two parameters as given below :

date – Specified date to be formatted.
format – Specified format.

MySQL DATE_FORMAT() Function Format Specifiers

This list of formats used in this function are listed below

Format	Description
%a	This abbreviation means weekday name. It’s limit is from Sun to Sat.
%b	This abbreviation means month name. It’s limit is from Jan to Dec.
%c	This abbreviation means numeric month name. It’s limit is from 0 to 12.
%D	This abbreviation means day of the month as a numeric value, followed by suffix like 1st, 2nd, etc.
%e	This abbreviation means day of the month as a numeric value. It’s limit is from 0 to 31.
%f	This abbreviation means microseconds. It’s limit is from 000000 to 999999.
%H	This abbreviation means hour. It’s limit is from 00 to 23.
%i	This abbreviation means minutes. It’s limit is from 00 to 59.
%j	This abbreviation means day of the year. It’s 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. It’s limit is from 00 to 59.
%U	This abbreviation means week where Sunday is the first day of the week. It’s limit is from 00 to 53.
%W	This abbreviation means weekday name from Sunday to Saturday.
%Y	This abbreviation means year as a numeric value of 4-digits.

MySQL DATE_FORMAT() Function

Let’s look at some examples of the DATE_FORMAT() function in MySQL.

Example 1

Getting a formatted year as “2020” from the specified date “2020-11-23”.

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

Output :

Example 2

Getting a formatted month name as “November” from the specified date “2020-11-23”.

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

Output :

November

Example 3

Getting a day of the month as a numeric value as “23rd” from the specified date “2020-11-23”.

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

Output :

23rd

Example-4 :

Getting month day and year as “November 23 2020” from the specified date “2020-11-23”.

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

Output :

November 23 2020

Important Points About MySQL DATE_FORMAT() Function

The MySQL DATE_FORMAT() function is used to format a date value based on a specific format.

The DATE_FORMAT() function returns a string whose character set and collation depend on the settings of the client’s connection2.

It will return NULL if any of the arguments are NULL.

The DATE_FORMAT() function is supported in MySQL from version 4.01.

MICROSECOND() Function in MySQL

K

Kanchan_Ray

Improve

Article Tags :

Similar Reads

FORMAT() function in MySQL

FORMAT() : This function in MySQL helps to format the given number like â€˜#, ###, ###.##'', round them to certain decimal points, and returns the result in the form of a string. Syntax : FORMAT(N, D, locale) Parameters : This function generally accepts three parameters. N - The number which is to be

FROM_DAYS() Function in MySQL

FROM_DAYS() : This function is used to return the date from a specified numeric date value. Here specified date value is divided by 365 and accordingly years, months, and days are returned. This function is used only with dates within the Gregorian calendar. Features : This function is used to find

GET_FORMAT() function in MySQL

GET_FORMAT() : This function in MySQL helps to convert date or time or DateTime in a formatted string for the specified arguments. The GET_FORMAT() function is more useful if it is used in combination with DATE_FORMAT() function. Syntax : GET_FORMAT({DATE | TIME | DATETIME}, {'EUR' | 'USA' | 'JIS' |

MySQL DATE_SUB() Function

The MySQL DATE_SUB() function subtracts a specified time or date interval from a datetime value. DATE_SUB() Function in MySQLThe DATE_SUB() Function in MySQL allows for various date and time calculations by subtracting time intervals. It is used to subtract a specified time or date interval from a g

DATE_FORMAT() Function in MariaDB

DATE_FORMAT() Function : In MariaDB, the DATE_FORMAT() function uses two parameters - a date as specified by a format mask. In this function, the first parameter will be a date and the second parameter will be the mask. This function will return the date in the given mask. This function will convert

DATEDIFF() Function in MySQL

DATEDIFF() function in MySQL is used to return the number of days between two specified date values. Syntax: DATEDIFF(date1, date2) Parameter: This function accepts two parameters as given below: date1: First specified datedate2: Second specified date Returns : It returns the number of days between

MySQL | CAST( ) Function

The MySQL CAST() function is used for converting a value from one datatype to another specific datatype. The CAST() function accepts two parameters which are the value to be converted and the datatype to which the value needs to be converted. The datatypes in which a given value can be converted are

MySQL | CONVERT( ) Function

The MySQL CONVERT() function is used for converting a value from one datatype to a different datatype. The MySQL CONVERT() function is also used for converting a value from one character set to another character set. It accepts two parameters which are the input value and the type to be converted in

CURDATE() Function in MySQL

The CURDATE() function in MYSQL is used to return the current date. The date is returned to the format of "YYYY-MM-DD" (string) or as YYYYMMDD (numeric). This function equals the CURRENT_DATE() function. In this article, we are going to discuss about CURDATE() function in detail. Syntax CURDATE(); P

EXTRACT() Function in MySQL

The EXTRACT() function in MySQL is a versatile tool used for retrieving specific components of date Whether we need the year, month, day or even the hour or minute. This function simplifies date manipulation and makes queries involving date and time data more efficient and easier to understand. In t