- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - GET_FORMAT() Function
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)