MariaDB Date_Format + 9 Useful Examples

by
MariaDB date_format dd/mm/yyexample

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.
VALUEDESCRIPTION
%aThe weekday names start with the abbreviation (Sun to Mon).
%bThe Month names start with the abbreviation (Jan to Dec).
%cThe month with a numeric value from 00 to 12.
%DThe day of the month is given as a suffix (1st,2nd,3rd, etc).
%dThe numeric value is given to the day of the month starting from 01 to 31.
%eMicroseconds starting from 000000 to 999999
%fAn hour numeric value is given from 00 to 23.
%HAn hour numeric value starting from 00 to 12.
%IAn hour numeric value starting from 00 to 12.
%iMinutes value starts from 00 to 59.
%jDay of the year starting from 001 to 366.
%kAn hour value starting from 00 to 23.
%lAn hour value starting from 1 to 12.
%MGiving the month full name like January to December
%mGive the month name in the numeric value from 00 to 12.
%pAM or PM.
%rTIme in 12 hours format with AM or PM (hh:mm: ss AM/PM).
%SSeconds value count from 00 to 59.
%sSeconds value count from 00 to 59.
%TTimes in 24 hours format (hh:mm:ss).
%UWhen Sunday is the first day of the week then the numeric value will start from(00 to 53).
%uWhen Monday is the first day of the week then the numeric value will start from (00 to 53).
%VWhen Sunday is the first day of the week then the numeric value will start from (01 to 53).
%vWhen Monday is the first day of the week then the numeric value will start from (01 to 53).
%WFull Weekday name as(Sunday to Saturday).
%wStarting Day of the Week where Sunday=0 and Saturday=6.
%XThe starting Year of the Week where Sunday is the first day of the week
%xThe starting Year of the Week where Monday is the first day of the week
%YYear value in 4 digits in numeric like 2012,2201,etc.
%yYear numeric value in last 2 digit like 89, 76, etc.
FORMAT_MASK Description of the DATE_FORMAT function

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;
MariaDB date_format function
MariaDB SELECT statement for EMPLOYEE table

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.

MariaDB date_format example
MariaDB Date_Format Function Example

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.

MariaDB date_format dd/mm/yyexample
MariaDB Date_Format dd/mm/yy Example

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.

MariaDB date_format yyyy-mm-dd example
MariaDB Date_Format yyyy-mm-dd Example

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.

MariaDB date_format where claUSE EXAMPLE
MariaDB Date_Format Function with WHERE clause Example

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.

MariaDB date_format now function example
MariaDB DATE_FORMAT function with NOW function Example

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.

MariaDB date_format locale example
MariaDB Date_Format function with Locale Example

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.

MariaDB date_format milliseconds example
MariaDB date_format milliseconds Example

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.

MariaDB date_format does not exist example
MariaDB Date_Format function does not exist Example

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.

MariaDB insert date_format example
MariaDB insert date_format example

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.

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