MariaDB Date_Format

In this MariaDB tutorial, we will examine how to utilize the MariaDB Date_Format Function and several examples of its usage.

MariaDB Date_format

In this part, we’ll learn about the date_format function, 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. The list of options as parameters is 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 the format_mask ‘%Y’ to give YEAR as the result set from the EMPLOYEE table.

The output 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. We will understand this implementation using an example from MariaDB.

A 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 4-digit year—2-digit month—2-digit date as yyyy-mm-dd by using the DATE_FORMAT function. We also used the alias name in the function’s result as the JOINING_YEAR field in the output.

Read: MariaDB Variables Tutorial

MariaDB date_format where clause

This section will explain how to use the DATE_FORMAT function with the WHERE clause in MariaDB.

The WHERE clause is used chiefly 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, the result set will have values like 2021 April 14th, etc.

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 in an example.

MariaDB’s NOW() function 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. This means that the NOW() function 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 country’s name.

The LOCALE keyword is explicitly used 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 is (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, and the LOCALE was used as de_DE to modify the German language for the German country. Using the SELECT query, it converted the day in the JOINING_DATETIME column into the German 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 into 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 provide an example of this implementation.

In MariaDB, the milliseconds come after seconds. They are very fast, and their starting point is 000000 to 999999 within 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. 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 millisecond records starting from 000000 from the EMPLOYEE table.

Read: MariaDB Enable Remote Access

MariaDB date_format does not exist

In this section, we’ll learn whether the DATE_FORMAT function exists and 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 the parameters provided within the code. If we just used the %Y as FORMAT MASK and the column_name, the query will be conducted for the table_name in the result set.

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 inserts 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 utilized the NOW() method inside the DATE FORMAT() function to acquire the date and time in YYYY-MM-DD HH:MM: SS format 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 a result, new records will be added to the table.

Read: MariaDB Delete Row + Examples

MariaDB Date_Format Timezone

In this section, we will discuss whether it is possible to use the timezone as a FORMAT MASK parameter in MariaDB’s DATE_FORMAT function.

In MariaDB, the timezone variable is used to track 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 per different countries.

If we need to change it, we need to use the TIMEZONE format function or TZ_timezone FUNCTION to change it according to the 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

In this MariaDB tutorial, we illustrated the use of the MariaDB Date_Format Function and discussed some sample examples.