How GET_FORMAT() function can combine with DATE_FORMAT() and STR_TO_DATE() function?

MySQLMySQLi Database

It is very useful to combine GET_FORMAT() function with DATE_FORMAT() and STR_TO_DATE() function.

Combining with DATE_FORMAT()

When it is combined with DATE_FORMAT() then it would arrange a particular date or time or datetime in a format obtained from GET_FORMAT() function.

mysql> Select DATE_FORMAT('2017-10-22',GET_FORMAT(date,'USA'))AS 'DATE IN USA FORMAT';
+-------------------+
| DATE IN USA FORMAT |
+-------------------+
| 10.22.2017 |
+-------------------+
1 row in set (0.00 sec)

The query above returns the given date in US format.

It is also used to retrieve the date values, in a particular format, stored in a table as follows −

mysql> Select * from date_testing1;
+-------+------------+
| Name | Date        |
+-------+------------+
| Ram   | 2017-05-03 |
| Shyam | 2003-10-31 |
+-------+------------+
2 rows in set (0.07 sec)

mysql> Select Name, Date, DATE_FORMAT(date,GET_FORMAT(date,'USA'))AS 'DATE IN US FORMAT'from date_testing1 Where Name='Ram';
+------+------------+-------------------+
| Name | Date       | DATE IN US FORMAT |
+------+------------+-------------------+
| Ram | 2017-05-03  | 05.03.2017        |
+------+------------+-------------------+
1 row in set (0.00 sec)

The query above returns the date in USA format where the name is ‘Ram’.

Combining with STR_TO_DATE()

It is used with STR_TO_DATE() function in a similar way as it is used with DATE_FORMAT() MySQL function.

mysql> Select STR_TO_DATE('22.10.2017',GET_FORMAT(DATE,'EUR'))AS 'EUROPEAN FORMAT';
+-----------------+
| EUROPEAN FORMAT |
+-----------------+
| 2017-10-22      |
+-----------------+
1 row in set (0.00 sec)

The query above returns the date in European format.

raja
Published on 19-Feb-2018 11:57:34
Advertisements