In MariaDB, GET_FORMAT() is a built-in date and time function that returns a format string.
This function can be useful for when working with functions that require a format string, such as DATE_FORMAT() and STR_TO_DATE().
Syntax
The syntax goes like this:
GET_FORMAT({DATE|DATETIME|TIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})Example
Here’s an example:
SELECT GET_FORMAT(DATE, 'EUR');Result:
+-------------------------+ | GET_FORMAT(DATE, 'EUR') | +-------------------------+ | %d.%m.%Y | +-------------------------+
This shows us the format to be used when working with dates in EUR format.
Here’s another example. This time we return the ISO format string for datetime:
SELECT GET_FORMAT(DATETIME, 'ISO');Result:
+-----------------------------+ | GET_FORMAT(DATETIME, 'ISO') | +-----------------------------+ | %Y-%m-%d %H:%i:%s | +-----------------------------+
With DATE_FORMAT()
In this example, I use GET_FORMAT() as the second argument to the DATE_FORMAT() function. This enables me to format the date without having to remember the actual format string to use.
SELECT DATE_FORMAT('2030-08-25', GET_FORMAT(DATE,'USA'));Result:
+---------------------------------------------------+
| DATE_FORMAT('2030-08-25', GET_FORMAT(DATE,'USA')) |
+---------------------------------------------------+
| 08.25.2030 |
+---------------------------------------------------+
Possible Format Strings
Here’s a table of the possible format strings:
| Arguments | Resulting Format |
|---|---|
DATE, 'EUR' | ‘%d.%m.%Y’ |
DATE, 'USA' | ‘%m.%d.%Y’ |
DATE, 'JIS' | ‘%Y-%m-%d’ |
DATE, 'ISO' | ‘%Y-%m-%d’ |
DATE, 'INTERNAL' | ‘%Y%m%d’ |
DATETIME, 'EUR' | ‘%Y-%m-%d %H.%i.%s’ |
DATETIME, 'USA' | ‘%Y-%m-%d %H.%i.%s’ |
DATETIME, 'JIS' | ‘%Y-%m-%d %H:%i:%s’ |
DATETIME, 'ISO' | ‘%Y-%m-%d %H:%i:%s’ |
DATETIME, 'INTERNAL' | ‘%Y%m%d%H%i%s’ |
TIME, 'EUR' | ‘%H.%i.%s’ |
TIME, 'USA' | ‘%h:%i:%s %p’ |
TIME, 'JIS' | ‘%H:%i:%s’ |
TIME, 'ISO' | ‘%H:%i:%s’ |
TIME, 'INTERNAL' | ‘%H%i%s’ |
Missing Argument
Calling GET_FORMAT() with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT GET_FORMAT();Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
And another example:
SELECT GET_FORMAT( DATE, 'JIS', 'OOPS' );Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' 'OOPS' )' at line 1