GET_FORMAT() function in MySQL

GET_FORMAT() :
This function in MySQL helps to convert date or time or DateTime in a formatted string for the specified arguments. The GET_FORMAT() function is more useful if it is used in combination with DATE_FORMAT() function.

Syntax :

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

Parameters :

• DATE | TIME | DATETIME – A date or time or DateTime.
• ‘EUR’ | ‘USA’ | ‘JIS’ | ‘ISO’ | ‘INTERNAL’ – Different formats used.

Returns :
The function will return a formatted string for the specified arguments.

Example-1 :
Basic usage of GET_FORMAT() function.

SELECT GET_FORMAT(DATE, 'EUR') 
As New_Format;

Output :

New_Format
‘%d.%m.%Y’

Example-2 :
Now using DATE_FORMAT() function along with GET_FORMAT().

SELECT DATE_FORMAT('2020-12-25', GET_FORMAT(DATE, 'USA')) 
AS 'New_Format';

Output :

New_Format
12.25.2020

Example-3 :
Usage of GET_FORMAT() function with Date values, which means the first argument is fixed as DATE and the second argument is changed continuously.

SELECT  
   GET_FORMAT(DATE, 'USA') AS 'USA_format',
   GET_FORMAT(DATE, 'JIS') AS 'JIS_format',
   GET_FORMAT(DATE, 'ISO') AS 'ISO_format',
   GET_FORMAT(DATE, 'EUR') AS 'EUR_format';

Output :

USA_format	JIS_format	ISO_format	EUR_format
‘%m.%d.%Y’	‘%Y-%m-%d’	‘%Y-%m-%d’	‘%d.%m.%Y’

Example-4 :
Usage of GET_FORMAT() function with DateTime values, which means the first argument is fixed as DATETIME and the second argument is changed continuously.

SELECT  
   GET_FORMAT(DATETIME, 'USA') AS 'USA_format',
   GET_FORMAT(DATETIME, 'JIS') AS 'JIS_format',
   GET_FORMAT(DATETIME, 'ISO') AS 'ISO_format',
   GET_FORMAT(DATETIME, 'EUR') AS 'EUR_format';

Output :

USA_format	JIS_format	ISO_format	EUR_format
‘%Y-%m-%d %H.%i.%s’	‘%Y-%m-%d %H:%i:%s’	‘%Y-%m-%d %H:%i:%s’	‘%Y-%m-%d %H.%i.%s’

Example-5 :
Usage of GET_FORMAT() function with Time values, which means the first argument is fixed as TIME and the second argument is changed continuously.

SELECT  
   GET_FORMAT(TIME, 'USA') AS 'USA_format',
   GET_FORMAT(TIME, 'JIS') AS 'JIS_format',
   GET_FORMAT(TIME, 'ISO') AS 'ISO_format',
   GET_FORMAT(TIME, 'EUR') AS 'EUR_format';

Output :

USA_format	JIS_format	ISO_format	EUR_format
‘%h:%i:%s %p’	‘%H:%i:%s’	‘%H:%i:%s’	‘%H.%i.%s’