MySQL GET_FORMAT() function

GET_FORMAT() function

MySQL GET_FORMAT() converts a date or time or datetime in a formatted manner as specified in the argument. This function is useful in combination with DATE_FORMAT(). The various formats have been described bellow. It helps determine the appropriate format for date and time representations based on different locale settings.

This function is useful in -

• The function helps ensure that date and time values are presented to users in a format they are familiar with and can easily understand.
• The function is valuable for international applications that need to display date and time values in various formats based on regional conventions.
• In systems where users can set their preferred date and time formats, GET_FORMAT() helps retrieve and apply those preferences.
• GET_FORMAT() ensures consistent formatting of date and time values throughout an application, regardless of the user's locale.
• GET_FORMAT() enables customization of date and time formatting based on different locales, allowing for consistent user experiences.
• GET_FORMAT() allows dynamic adjustment of date and time formatting without hardcoding specific formats.
• When generating reports or outputs that involve date and time values, GET_FORMAT() ensures that the format aligns with the user's locale.

Syntax:

GET_ FORMAT([date | time | datetime ],[‘EUR’ |‘USA’ |’JIS’|’ISO’|‘INTERNAL’])

Arguments:

Pictorial Presentation:

Different function calls

MySQL Version: 8.0

Example: MySQL GET_FORMAT() function

The following statement will arrange the date format in EUR.

Code:

SELECT GET_FORMAT(DATE,'EUR');

Output:

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

Example: GET_FORMAT() function with DATE_FORMAT()

The following statement will format and return the specified date 2009-05-18 in the format obtained from GET_FORMAT(DATE,'EUR').

Code:

SELECT DATE_FORMAT('2009-05-18',GET_FORMAT(DATE,'EUR'));

Output:

mysql> SELECT DATE_FORMAT('2009-05-18',GET_FORMAT(DATE,'EUR'));
+--------------------------------------------------+
| DATE_FORMAT('2009-05-18',GET_FORMAT(DATE,'EUR')) |
+--------------------------------------------------+
| 18.05.2009                                       | 
+--------------------------------------------------+
1 row in set (0.00 sec)

Example: GET_FORMAT() function using 'USA' format

The following statement will arrange the time format in USA format.

Code:

SELECT GET_FORMAT(TIME,'USA');

Output:

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

Example: GET_FORMAT() function with STR_TO_DATE()

The following statement will format and return the specified time 11:15:46 PM in a specific format as obtained from STR_TO_DATE(TIME,'USA').

Code:

SELECT STR_TO_DATE('11:15:46 PM',GET_FORMAT(TIME,'USA'));

Output:

mysql> SELECT STR_TO_DATE('11:15:46 PM',GET_FORMAT(TIME,'USA'));
+---------------------------------------------------+
| STR_TO_DATE('11:15:46 PM',GET_FORMAT(TIME,'USA')) |
+---------------------------------------------------+
| 23:15:46                                          | 
+---------------------------------------------------+
1 row in set (0.00 sec)

Example : GET_FORMAT() function in 'JIS' format

The following statement will arrange the datetime format in JIS format.

Code:

SELECT GET_FORMAT(DATETIME,'JIS');

Output:

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

Example : GET_FORMAT() function with DATE_FORMAT() and 'JIS' format

The following statement will arrange the datetime 2009-05-18 11:15:46 in a format obtained from GET_FORMAT(DATETIME,'JIS').

Code:

SELECT DATE_FORMAT('2009-05-18 11:15:46',GET_FORMAT(DATETIME,'JIS'));

Output:

mysql> SELECT DATE_FORMAT('2009-05-18 11:15:46',GET_FORMAT(DATETIME,'JIS'));
+---------------------------------------------------------------+
| DATE_FORMAT('2009-05-18 11:15:46',GET_FORMAT(DATETIME,'JIS')) |
+---------------------------------------------------------------+
| 2009-05-18 11:15:46                                           | 
+---------------------------------------------------------------+
1 row in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: FROM_UNIXTIME()
Next: HOUR()