MySQL DATE_FORMAT() Function

17 Mar 2025 | 2 min read

The date_format() is a MySQL date/time function. It is used to get the date in specified format.

Syntax

Parameter:

Date: date to be formatted

Format_mask: format to be applied in date, following is the list of all the formats.

Value	Description
%a	Weekday name abbreviated (Sun to Sat)
%b	Month name abbreviated (Jan to Dec)
%c	Month as a numeric value (0 to 12)
%D	Day of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%d	Day of the month as a numeric value (01 to 31)
%e	Day of the month as a numeric value (0 to 31)
%f	Microseconds (000000 to 999999) %f is available starting in MySQL 4.1.1
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%j	Day of the year (001 to 366)
%k	Hour (00 to 23)
%l	Hour (1 to 12)
%M	Month name in full (January to December)
%m	Month name as a numeric value (00 to 12)
%p	AM or PM
%r	Time in 12-hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss)
%U	Week where Sunday is the first day of the week (00 to 53)
%u	Week where Monday is the first day of the week (00 to 53)
%V	Week where Sunday is the first day of the week (01 to 53) Available starting in MySQL 3.23.8 and used with %X
%v	Week where Monday is the first day of the week (01 to 53) Available starting in MySQL 3.23.8 and used with %X
%W	Weekday name in full (Sunday to Saturday)
%w	Day of the week where Sunday=0 and Saturday=6
%X	Year for the week where Sunday is the first day of the week Available starting in MySQL 3.23.8 and used with %V
%x	Year for the week where Monday is the first day of the week Available starting in MySQL 3.23.8 and used with %v
%Y	Year as a numeric, 4-digit value
%y	Year as a numeric, 2-digit value

Example 1

Output:

Example 2

Output:

Example 3

Output:

Example 4

Output:

Example 5

Output:

Next TopicMySQL date/time

← prev next →

CURDATE() function
MySQL CURDATE() Function The curdate() is a my date/time function. It is used to get the current date. Syntax select curdate(); Parameters NA Returns This function returns the current date. Example 1 Select curdate(); Output: Example 2 Select curdate()+2; Output: TopicMySQL date/time ...
1 min read
Subtime() function
MySQL Date/Time Subtime() Function The subtime() is a MySQL date/time function. It is used to get the time/datetime which is subtracted by certain intervals. Syntax select subtime(start_value, time); Parameter: Start_value: time/datetime by which interval is to be subtracted. Time: time in which interval is to be subtracted. Returns This function returns the time/datetime which...
1 min read
timestamp() function
MySQL Date/Time timestamp() Function The timediff() is a MySQL date/time function. It is used to convert the expression into datetime/time. Syntax select timestamp(expression, [interval]) Parameter: Expression : time/datetime value to be converted Interval : it is optional, it is added into time value Returns: This function returns a datetime value. Example 1 select timestamp('2018-10-03'); Output: Example 2 select timestamp('2018-10-03...
1 min read
localtime() function
MySQL Date/Time localtime() Function The localtime() is a MySQL date/time function. It is used to get the current date and time. Syntax select localtime(); Returns: This function returns the current date and time. Example 1 Select localtime(); Output: Example 2 Select localtime()+2; Output: TopicMySQL datetime ...
1 min read
DAYOFWEEK() function
MySQL DAYOFWEEK() Function The dayofweek() is a MySQL date/time function. It is used to get the day of the week in numeric. Syntax select dayofweek(date); Parameter: Date: date value for getting a day of the week. Returns: This function returns the weekday index for a date value. Example 1 Select dayofweek('2018-09-26'); Output: Example 2 Select dayofweek('2018-5-12'); Output: TopicMySQL date/time ...
1 min read
DAY() function
MySQL DAY() Function The day() is a MySQL date/time function. It is used to get the day from the given date. Syntax select day('date_value'); Parameter: date_value: date for getting day. Returns This function returns the day of the month (a number from 1 to 31) given a date value. Example 1 Select day('2018-09-25'); Output: Example 2 Select day('2018-09-10'); Output: ...
1 min read
DATE_ADD() function
MySQL DATE_ADD() Function The date_add() is a MySQL date/time function. It is used to get the date in which some date/datetime intervals are added. Syntax select date_add(date, interval value unit); Parameter: Date: date in which interval will be added Value: date/time intervals Unit: type of interval which are as following: Unit Compatibility MICROSECOND 4.1.1+ SECOND 3.2.3+ MINUTE 3.2.3+ HOUR 3.2.3+ DAY 3.2.3+ WEEK 5+ MONTH 3.2.3+ QUARTER 5+ YEAR 3.2.3+ SECOND_MICROSECOND 4.1.1+ MINUTE_MICROSECOND 4.1.1+ MINUTE_SECOND 4.1.1+ HOUR_MICROSECOND 4.1.1+ HOUR_SECOND 4.1.1+ HOUR_MINUTE 3.2.3+ DAY_MICROSECOND 4.1.1+ DAY_SECOND 3.2.3+ DAY_MINUTE 3.2.3+ DAY_HOUR 3.2.3+ YEAR_MONTH 3.2.3+ Returns This function will return...
1 min read
monthname() function
MySQL Date/Time monthname() Function The monthname() is a MySQL date/time function. It is used to get the full month name. Syntax select monthname(date_value); Parameter: date_value : datetime or time value for getting month name. Returns: This function returns the full name of the month for a date. Example 1 Select monthname ('2018-9-28 10:20:25.000002'); Output: Example 2 Select monthname('1995-2-11'); Output: ...
1 min read
time_format() function
MySQL Date/Time time_format() Function The time_format() is a MySQL date/time function. It is used to format the time in specified given format_mask. Syntax select time_format(time, format_mask) Parameter: Time: time to be format Format_mask: format to be applied on time following are the list of options VALUE DESCRIPTION %f Microseconds (000000 to 999999) %f is available starting in...
1 min read
ADDDATE() function
MySQL ADDDATE() Function The adddate() is a MySQL date/time function. It is used to get the date in which some time/date intervals are added. Syntax select adddate(date, interval value unit ); Or select adddate(date, days); Parameters: Date: date to be added in intervals Days: number of days to be added in date Value: value of...
1 min read

MySQL DATE_FORMAT() Function

Syntax

Parameter:

Example 1

Example 2

Example 3

Example 4

Example 5

Contact info

Follow us

Tutorials

Interview Questions

Online Compiler

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

MySQL Tutorial

MySQL Database

Table & Views

MySQL Queries

MySQL Clauses

MySQL Conditions

MySQL Join

MySQL Key

MySQL Privileges

MySQL Indexes

MySQL Triggers

User Management

MySQL Workbench

FULLTEXT Search

Regular Expressions

MySQL Globalisation

Interview Questions

Aggregate Functions

Control Flow Function

MSQL String Functions

MySQL Math Functions

MySQL Date/Time Functions

Differences

MySQL Misc

MySQL DATE_FORMAT() Function

Syntax

Parameter:

Example 1

Example 2

Example 3

Example 4

Example 5

Related Posts

CURDATE() function

Subtime() function

timestamp() function

localtime() function

DAYOFWEEK() function

DAY() function

DATE_ADD() function

monthname() function

time_format() function

ADDDATE() function

Subscribe to Tpoint Tech

Contact info

Follow us

Tutorials

Interview Questions

Online Compiler