In this MariaDB tutorial, we will look at how to utilize the MariaDB Date_Format Function and we will also look at several examples related to its usage. There are lists of the topic that comes under discussion:
- MariaDB date_format
- MariaDB date_format dd/mm/yy
- MariaDB date_format yyyy-mm-dd
- MariaDB date_format where clause
- MariaDB date_format now
- MariaDB date_format locale
- MariaDB date_format milliseconds
- MariaDB date_format does not exist
- MariaDB insert date format
- MariaDB date_format timezone
MariaDB Date_format
Here we’ll learn about the date_format function in this part, which is described using syntax and an example.
The date_format function in MariaDB formats the date according to a format filter. The date_format function in MariaDB has the following syntax:
SYNTAX:
DATE_FORMAT (DATE, FORMAT_MASK);
The syntax explanation:
- date_format: The date is wanted to format.
- format_mask: The format to apply on a date. There is a list of options as parameters in the following table. This parameter can be used in many combinations.
VALUE | DESCRIPTION |
%a | The weekday names start with the abbreviation (Sun to Mon). |
%b | The Month names start with the abbreviation (Jan to Dec). |
%c | The month with a numeric value from 00 to 12. |
%D | The day of the month is given as a suffix (1st,2nd,3rd, etc). |
%d | The numeric value is given to the day of the month starting from 01 to 31. |
%e | Microseconds starting from 000000 to 999999 |
%f | An hour numeric value is given from 00 to 23. |
%H | An hour numeric value starting from 00 to 12. |
%I | An hour numeric value starting from 00 to 12. |
%i | Minutes value starts from 00 to 59. |
%j | Day of the year starting from 001 to 366. |
%k | An hour value starting from 00 to 23. |
%l | An hour value starting from 1 to 12. |
%M | Giving the month full name like January to December |
%m | Give the month name in the numeric value from 00 to 12. |
%p | AM or PM. |
%r | TIme in 12 hours format with AM or PM (hh:mm: ss AM/PM). |
%S | Seconds value count from 00 to 59. |
%s | Seconds value count from 00 to 59. |
%T | Times in 24 hours format (hh:mm:ss). |
%U | When Sunday is the first day of the week then the numeric value will start from(00 to 53). |
%u | When Monday is the first day of the week then the numeric value will start from (00 to 53). |
%V | When Sunday is the first day of the week then the numeric value will start from (01 to 53). |
%v | When Monday is the first day of the week then the numeric value will start from (01 to 53). |
%W | Full Weekday name as(Sunday to Saturday). |
%w | Starting Day of the Week where Sunday=0 and Saturday=6. |
%X | The starting Year of the Week where Sunday is the first day of the week |
%x | The starting Year of the Week where Monday is the first day of the week |
%Y | Year value in 4 digits in numeric like 2012,2201,etc. |
%y | Year numeric value in last 2 digit like 89, 76, etc. |
NOTE:
- Date and month start with 00 allows storing incomplete in MariaDB.
- It is compatible with MariaDB version 10.
First, let’s create an EMPLOYEE table by using the following query:
create table Employee (
emp_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
emp_email VARCHAR(50),
emp_gender VARCHAR(50),
joining_datetime DATETIME
);
insert into Employee (emp_id, first_name, last_name, emp_email, emp_gender, joining_datetime)
values (1, 'Leonerd', 'Demanche', 'ldemanche0@fotki.com', 'Female', '2022-03-04 02:35:41'),
(2, 'Josee', 'MacNeish', 'jmacneish1@dropbox.com', 'Male', '2021-10-11 05:18:41'),
(3, 'Arlinda', 'Kiwitz', 'akiwitz2@prweb.com', 'Male', '2022-03-11 18:15:40'),
(4, 'Leif', 'Sottell', 'lsottell3@friendfeed.com', 'Female', '2021-07-03 02:25:38'),
(5, 'Christen', 'Karpman', 'ckarpman4@paginegialle.it', 'Female', '2021-08-05 23:28:07'),
(6, 'Tome', 'Croix', 'tcroix5@oaic.gov.au', 'Female', '2021-03-09 18:52:27'),
(7, 'Zarah', 'Yoskowitz', 'zyoskowitz6@youtube.com', 'Female', '2021-10-17 19:18:56'),
(8, 'Erskine', 'Shillaker', 'eshillaker7@eepurl.com', 'Male', '2022-01-06 22:48:47'),
(9, 'Ora', 'Landers', 'olanders8@dedecms.com', 'Male', '2021-05-10 12:04:40'),
(10, 'Curr', 'Ricciardo', 'cricciardo9@delicious.com', 'Female', '2021-11-28 06:42:24'),
(11, 'Sherwin', 'McRae', 'smcraea@sfgate.com', 'Male', '2021-06-21 15:24:31'),
(12, 'Russ', 'Tousy', 'rtousyb@mail.ru', 'Male', '2021-10-08 03:07:54'),
(13, 'Antons', 'Tolmie', 'atolmiec@foxnews.com', 'Female', '2021-12-04 10:48:58'),
(14, 'Leonie', 'Sorensen', 'lsorensend@icq.com', 'Male', '2021-06-10 02:20:11'),
(15, 'Jordan', 'Channon', 'jchannone@spotify.com', 'Female', '2021-12-18 15:57:24'),
(16, 'Anson', 'Lockwood', 'alockwoodf@usda.gov', 'Male', '2021-07-07 17:05:38'),
(17, 'Gasper', 'Catchpole', 'gcatchpoleg@ed.gov', 'Female', '2022-02-07 20:39:13'),
(18, 'Althea', 'Lisciardelli', 'alisciardellih@chron.com', 'Male', '2021-10-29 04:52:20'),
(19, 'Glynda', 'Matthias', 'gmatthiasi@irs.gov', 'Male', '2021-09-20 14:25:30'),
(20, 'Lyman', 'Lackmann', 'llackmannj@amazon.com', 'Female', '2021-11-13 16:11:08'),
(21, 'Neala', 'Lamblot', 'nlamblotk@i2i.jp', 'Female', '2021-06-11 18:00:18'),
(22, 'Raina', 'Bertot', 'rbertotl@howstuffworks.com', 'Male', '2021-11-02 09:23:24'),
(23, 'Ban', 'Clemenza', 'bclemenzam@seattletimes.com', 'Male', '2021-05-07 04:58:09'),
(24, 'Aarika', 'Crowcher', 'acrowchern@wikipedia.org', 'Male', '2021-08-11 05:42:11'),
(25, 'Alicia', 'Bastow', 'abastowo@hibu.com', 'Male', '2022-03-08 08:36:14'),
(26, 'Nathaniel', 'Berni', 'nbernip@tamu.edu', 'Female', '2022-02-27 17:36:30'),
(27, 'Dominique', 'Fender', 'dfenderq@simplemachines.org', 'Non-binary', '2021-12-23 00:51:20'),
(28, 'Cordi', 'Thorrington', 'cthorringtonr@topsy.com', 'Female', '2021-08-31 06:12:50'),
(29, 'Rance', 'Barling', 'rbarlings@house.gov', 'Male', '2021-12-08 20:41:33'),
(30, 'Lewie', 'Walthew', 'lwalthewt@mayoclinic.com', 'Female', '2021-07-20 08:24:10');
SELECT * FROM EMPLOYEE;

The following is an example of the DATE_FORMAT function in MariaDB:
EXAMPLE:
SELECT DATE_FORMAT(joining_datetime,'%Y') AS JOINIG_YEAR
FROM EMPLOYEE;
As we see in the above query, we have used the DATE_FORMAT function on the JOINING_DATETTIME column and by using the format_mask as ‘%Y’ to give YEAR as the result set from the EMPLOYEE table.
In the output, it will give years from the JOINING_DATETIME column but as the JOINING_YEAR column because of the alias_name.

Also, check: MariaDB Backup Database
MariaDB date_format dd/mm/yy
In this section, we’ll learn how to convert dates to dd/mm/yy format using the date_format function in MariaDB. And we will understand this implementation using an example in MariaDB.
The sample example of the date_format function is given below:
EXAMPLE:
SELECT DATE_FORMAT(joining_datetime,'%d/%m/%y') AS JOINIG_YEAR
FROM EMPLOYEEE LIMIT 5;
In the above query, we have formatted the joining_datetime column in ‘%d/%m/%y’ as format_mask by using the DATE_FORMAT function. And we have also given the alias name of the joining_datetime column as joining_year.
It means that the date_format function will return the values of the JOINING_YEAR column in dd/mm/yy format.

Read: MariaDB JSON Function
MariaDB date_format yyyy-mm-dd
We’ll learn how to format a DateTime value in the yyyy-mm-dd format using the date_format function in MariaDB.
The following is an example of how to use the DATE_FORMAT function to convert the DateTime value to yyyy-mm-dd:
EXAMPLE:
SELECT DATE_FORMAT(joining_datetime,'%Y-%m-%d') AS JOINIG_YEAR
FROM EMPLOYEE
LIMIT 10;
In the above query, we have converted the JOINING_DATETITME column into a 4digit year– 2digit month– 2digit date as yyyy-mm-dd by using the DATE_FORMAT function. And we also used the alias name on the result of the function as the JOINING_YEAR field in the output.

Read: MariaDB Variables Tutorial
MariaDB date_format where clause
In this section, we will understand how to use the DATE_FORMAT function with the WHERE clause in MariaDB.
The WHERE clause is mostly used in MariaDB to obtain records depending on conditions. The following is the syntax of the MariaDB DATE_FORMAT function with the WHERE clause:
SYNTAX:
SELECT DATE_FORMAT(COLUMN_NAME, FORMAT_MASK)
FROM TABLE_NAME
[WHERE conditions];
The following is an example of the MariaDB DATE_FORMAT function with the WHERE clause:
EXAMPLE:
SELECT DATE_FORMAT(JOINING_DATETIME,'%Y %M %D')
FROM EMPLOYEE
WHERE EMP_ID<=10;
In the above query, we have used the DATE_FORMAT function on the JOINING_DATETIME column by format_mask as ‘%Y %M %D’ with the WHERE condition of the EMP_ID column being greater than or equal to 10.
In the WHERE condition, if the emp_id column matches then it will give values like 2021 April 14th, etc in the result set.
As a result, it brings the top 10 columns from the EMPLOYEE table by using the DATE_FORMAT function.

Read: MariaDB Drop Table + Examples
MariaDB date_format now
Here we will learn how to use the DATE_FORMAT function in MariaDB with the NOW function, which is also demonstrated with an example.
The NOW() function in MariaDB is used to get the computer’s current date and time. The syntax of the DATE_FORMAT function in conjunction with the NOW() function is as follows:
SYNTAX:
SELECT DATE_FORMAT(COLUMN_NAME,NOW()) FROM TABLE_NAME
[WHERE conditions];
The DATE_FORMAT function is used with the NOW function in the following example:
EXAMPLE:
SELECT DATE_FORMAT(JOINING_DATETIME,NOW())
FROM EMPLOYEE LIMIT 5;
As we see in the above query, we have created and used the DATE_FORMAT function on the joining_datetime column with the NOW() function from the EMPLOYEE table. It means that with the help of the NOW() function, it will provide the current date and time of the JOINING_DATETIME column of the EMPLOYEE table with the help of the DATE_FORMAT function.
The main purpose of the LIMIT clause is to provide the top 5 records of the EMPLOYEE table based on the query.

Read: MariaDB Temporary Table
MariaDB date_format locale
In this section, we will learn how to use the date_format function in MariaDB for the locale in the name of the country.
The LOCALE keyword in MariaDB is used explicitly as the third input in MariaDB’s DATE_FORMAT function. This makes the function session-independent, and the three-argument version of DATE_FORMAT() can be used to create virtual indexes and persistently produced columns.
The example of the third argument used in the DATE_FORMAT function like (zn-CH for Chinese which is used in CHINA, de-DE for german language which is used in GERMANY, etc).
The LOCALE keyword `syntax for the DATE_FORMAT() function is as follows:
SYNTAX:
SELECT DATE_FORMAT(COLUMN_NAME, DATE MASK,LOCALE)
FROM TABLE_NAME;
The following is an example of using the DATE_FORMAT function with the LOCALE:
EXAMPLE:
SELECT DATE_FORMAT(JOINING_DATETIME,'%W','de_DE')
FROM EMPLOYEE
LIMIT 5;
The DATE_FORMAT() function was used in the above query, together with the LOCALE as de_DE, to modify the German language for the German country. Using the SELECT query, it converted the JOINING_DATETIME column’s day into the Deutsch language from the EMPLOYEE table.
The main purpose of the LIMIT clause as LIMIT 5 is to bring the top 5 records for the output in the resultset, with the help of the SELECT statement.

Read: MariaDB Reset Root Password
MariaDB date_format milliseconds
We’ll learn how to use the DATE_FORMAT function in MariaDB to get milliseconds. Moreover, we will discuss the syntax and an example for this implementation.
In MariaDB, the milliseconds come after seconds. They are very fast and their starting point is 000000 to 999999 in the time frame. The following is the syntax for using the DATE_FORMAT function to calculate milliseconds:
SYNTAX:
SELECT DATE_FORMAT(COLUMN_NAME,FORMAT MASK)
FROM TABLE_NAME;
The following is an example of how to use the DATE_FORMAT function to calculate milliseconds:
EXAMPLE:
SELECT DATE_FORMAT(JOINING_DATETIME,'%f')
FROM EMPLOYEE
LIMIT 5;
Using the SELECT statement, we utilized the DATE_FORMAT function on the JOINING_DATETIME column in the EMPLOYEE table with format_mask as %f in the query. Using the SELECT statement, we also utilized the LIMIT clause like LIMIT 5 to extract the top 5 records from the EMPLOYEE table.
As a result, it will give the top 5 records of milliseconds started from 000000 from the EMPLOYEE table.

Read: MariaDB Enable Remote Access
MariaDB date_format does not exist
In this section, we’ll learn whether or not the DATE_FORMAT function exists, as well as how to use it with an example.
The following is a sample example of the MariaDB DATE_FORMAT function that does not exist:
EXAMPLE:
SELECT DATE_FORMAT (joining_datetime,'%Y','el-GR') as JOINING_FORYEAR
FROM EMPLOYEE;
We used the DATE_FORMAT function on the joining_date column of the EMPLOYEE table with the Format mask as ‘%Y‘. Because the date_format does not exist in MariaDB, it returned an error due to the execution time.
The DATE_FORMAT function is returning an error due to parameters provided within the code. The query will be conducted for the table_name in the result set if we just used the %Y as FORMAT MASK and the column_name.

Read: MariaDB ISNULL + Examples
MariaDB insert date format
We’ll learn how to utilize the INSERT statement with the DATE_FORMAT function in MariaDB, which is described with syntax and examples.
The INSERT INTO statement in MariaDB is used to insert one or more records into a table. The following is the MariaDB INSERT INTO statement syntax with the DATE_FORMAT function:
SYNTAX:
INSERT INTO TABLE_NAME(COLUMN_LIST,....)
VALUES(VALUE_LIST,...,DATE_FORMAT(NOW(),DATE MASK);
The following is an example of a MariaDB INSERT INTO statement using the DATE_FORMAT function:
EXAMPLE:
INSERT INTO employee (emp_id,first_name,last_name,emp_email,emp_gender,joining_datetime)
VALUES
(31,'John','Wick','johnnathan@usaEmbassy.com','Male',DATE_FORMAT('2022-03-24 17:23:56','%Y-%m-%d'),
(31,'John','Wick','johnnathan@usaEmbassy.com','Male',date_format(NOW(),'%Y-%m-%d');
SELECT * FROM EMPLOYEE;
We simply utilized the NOW() method inside the DATE FORMAT() function to acquire the date and time in the format of YYYY-MM-DD HH:MM: SS to enter into a new record using the INSERT INTO statement in the preceding query.
If we want to see if a new record has been inserted into the EMPLOYEE table, we may use the SELECT query to retrieve all records from the EMPLOYEE table. As the resultset, it will add new records to the table.

Read: MariaDB Delete Row + Examples
MariaDB Date_Format Timezone
In this section, we will discuss is it possible to use the timezone as a FORMAT MASK parameter in the DATE_FORMAT function of MariaDB.
In MariaDB, the timezone variable is used to maintain track of several timezone settings. There are 2 ways to connect global time zone setting i.e; global and system. There is no format in the DATE_FORMAT function to convert the timezone as per different countries.
If we need to change it then we need to use the TIMEZONE format function or TZ_timezone FUNCTION to change it as per requirements.
Also, take a look at some more MariaDB tutorials.
- MariaDB Rename Index
- MariaDB Str_To_Date
- How to Create Database in MariaDB
- MariaDB ENUM – Helpful Guide
- MariaDB JSON Function + Examples
So, in this MariaDB tutorial, we have illustrated the use of the MariaDB Date_Format Function and also discussed some sample examples. There are lists of the topic that comes under discussion:
- MariaDB date_format
- MariaDB date_format dd/mm/yy
- MariaDB date_format yyyy-mm-dd
- MariaDB date_format where clause
- MariaDB date_format now
- MariaDB date_format locale
- MariaDB date_format milliseconds
- MariaDB date_format does not exist
- MariaDB insert date format
- MariaDB date_format timezone
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.