MySQL - GET_FORMAT() Function

Previous
Next

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL GET_FORMAT() function is used to retrieve the DATE or, TIME or, DATETIME format string of the date-time standards such as USA or, JIS or, ISO or, EUR etc.…

Syntax

Following is the syntax of the above function –

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'});

Example 1

Following example demonstrates the usage of the GET_FORMAT() function. It prints the date format string according to the USA standards –

mysql> SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
|                %m.%d.%Y |
+-------------------------+
1 row in set (2.23 sec)

Example 2

Following query prints the DATE format strings of various standards –

mysql> SELECT GET_FORMAT(DATE, 'JIS');
+-------------------------+
| GET_FORMAT(DATE, 'JIS') |
+-------------------------+
|%Y-%m-%d                 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATE, 'ISO');
+-------------------------+
| GET_FORMAT(DATE, 'ISO') |
+-------------------------+
|%Y-%m-%d                 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATE, 'EUR');
+-------------------------+
| GET_FORMAT(DATE, 'EUR') |
+-------------------------+
| %d.%m.%Y                |
+-------------------------+
1 row in set (0.00 sec)

Example 3

Following query prints the DATETIME format strings of various standards –

mysql> SELECT GET_FORMAT(DATETIME, 'USA');
+-----------------------------+
| GET_FORMAT(DATETIME, 'USA') |
+-----------------------------+
| %Y-%m-%d %H.%i.%s           |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATETIME, 'JIS');
+-----------------------------+
| GET_FORMAT(DATETIME, 'JIS') |
+-----------------------------+
| %Y-%m-%d %H:%i:%s           |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATETIME, 'ISO');
+-----------------------------+
| GET_FORMAT(DATETIME, 'ISO') |
+-----------------------------+
| %Y-%m-%d %H:%i:%s           |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATETIME, 'EUR');
+-----------------------------+
| GET_FORMAT(DATETIME, 'EUR') |
+-----------------------------+
| %Y-%m-%d %H.%i.%s           |
+-----------------------------+
1 row in set (0.00 sec)

Example 4

Following query prints the TIME format strings of various standards –

mysql> SELECT GET_FORMAT(TIME, 'USA');
+-------------------------+
| GET_FORMAT(TIME, 'USA') |
+-------------------------+
| %h:%i:%s %p             |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(TIME, 'JIS');
+-------------------------+
| GET_FORMAT(TIME, 'JIS') |
+-------------------------+
| %H:%i:%s                |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(TIME, 'ISO');
+-------------------------+
| GET_FORMAT(TIME, 'ISO') |
+-------------------------+
| %H:%i:%s                |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(TIME, 'EUR');
+-------------------------+
| GET_FORMAT(TIME, 'EUR') |
+-------------------------+
| %H.%i.%s                |
+-------------------------+
1 row in set (0.00 sec)

Example 5

Following query prints the format stings of 'INTERNAL' format of date, time and date time values –

mysql> SELECT GET_FORMAT(DATE,'INTERNAL');
+-----------------------------+
| GET_FORMAT(DATE,'INTERNAL') |
+-----------------------------+
| %Y%m%d                      |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATETIME,'INTERNAL');
+---------------------------------+
| GET_FORMAT(DATETIME,'INTERNAL') |
+---------------------------------+
| %Y%m%d%H%i%s                    |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(TIME,'INTERNAL');
+-----------------------------+
| GET_FORMAT(TIME,'INTERNAL') |
+-----------------------------+
| %H%i%s                      |
+-----------------------------+
1 row in set (0.00 sec)

Example 6

You can also use this function in combination with DATE_FORMAT() and STR_TO_DATE(). You can pass this as the format string.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', GET_FORMAT(DATE, 'ISO'));
+-------------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', GET_FORMAT(DATE, 'ISO')) |
+-------------------------------------------------------------+
| 1997-10-04                                                  |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('09.27.1991',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('09.27.1991',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 1991-09-27                                       |
+--------------------------------------------------+
1 row in set (0.00 sec)

Example 7

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query prints the date of birth of the employees as per EUR standard –

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, DATE_FORMAT(Date_Of_Birth, GET_FORMAT(DATE, 'EUR')) as FormattedDOB FROM MyPlayers;
+------------+------------+---------------+-------------+--------------+
| First_Name | Last_Name  | Date_Of_Birth | Country     | FormattedDOB |
+------------+------------+---------------+-------------+--------------+
| Shikhar    | Dhawan     | 1981-12-05    | India       | 05.12.1981   |
| Jonathan   | Trott      | 1981-04-22    | SouthAfrica | 22.04.1981   |
| Kumara     | Sangakkara | 1977-10-27    | Srilanka    | 27.10.1977   |
| Virat      | Kohli      | 1988-11-05    | India       | 05.11.1988   |
| Rohit      | Sharma     | 1987-04-30    | India       | 30.04.1987   |
| Ravindra   | Jadeja     | 1988-12-06    | India       | 06.12.1988   |
| James      | Anderson   | 1982-06-30    | England     | 30.06.1982   |
+------------+------------+---------------+-------------+--------------+
7 rows in set (0.00 sec)

Example 8

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date,
	SubscriptionTime time
);
insert into SubscribersData values('Raja', 'Premium', Date('2020-10-21'), Time('20:53:49'));
insert into SubscribersData values('Roja', 'Basic', Date('2020-11-26'), Time('10:13:19'));
insert into SubscribersData values('Puja', 'Moderate', Date('2021-03-07'), Time('05:43:20'));
insert into SubscribersData values('Vanaja', 'Basic', Date('2021-02-21'), Time('16:36:39'));
insert into SubscribersData values('Jalaja', 'Premium', Date('2021-01-30'), Time('12:45:45'));

In the following example we are trying to format the SubscriptionDate and SubscriptionTime according to the USA standard –

mysql> SELECT SubscriberName, PackageName,
DATE_FORMAT(SubscriptionDate, GET_FORMAT(DATE, 'USA')) as USA_Date,
DATE_FORMAT(SubscriptionTime, GET_FORMAT(TIME, 'USA')) as USA_Time
FROM SubscribersData;
+----------------+-------------+------------+-------------+
| SubscriberName | PackageName | USA_Date   | USA_Time    |
+----------------+-------------+------------+-------------+
| Raja           | Premium     | 10.21.2020 | 08:53:49 PM |
| Roja           | Basic       | 11.26.2020 | 10:13:19 AM |
| Puja           | Moderate    | 03.07.2021 | 05:43:20 AM |
| Vanaja         | Basic       | 02.21.2021 | 04:36:39 PM |
| Jalaja         | Premium     | 01.30.2021 | 12:45:45 PM |
+----------------+-------------+------------+-------------+
5 rows in set (0.00 sec)
mysql-date-time-functions.htm
Print Page
Previous
Next
Advertisements