GET_FORMAT() function in MySQL
Last Updated :
10 Dec, 2020
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 :
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 :
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’ |
Share your thoughts in the comments
Please Login to comment...