Converting date in MySQL query

1

Dear All,

I have some report that was spooled from ERPNext into Metabase, but i want i want to convert a date to seperate entity like YEAR, Month and Day intead of Monday 17 October, 2017.

I have tried;

TO_CHAR(c.post_date, 'DD-MM-YYYY') AS "CUST_CREATED_DATE"
TO_CHAR(c.post_date, 'DD') AS "CUST_CREATED_DAY",
TO_CHAR(c.post_date, 'MM') AS "CUST_CREATED_MONTH",
TO_CHAR(c.post_date, 'YYYY') AS "CUST_CREATED_YEAR",

I want to achieve this so that i can add my period (YEAR, MONTH and DAY) to my Chart or Table in my dashboard
What is the best practice to that.

2

Assuming you got something like this:

MariaDB [d56cb677eaab3383]> SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM dual;
ERROR 1305 (42000): FUNCTION d56cb677eaab3383.TO_CHAR does not exist

This may explain your case TO_CHAR - Convert Datetime to String - Oracle to MySQL Migration - SQLines Tools

In Oracle, TO_CHAR function converts a datetime value to string using the specified format. In MySQL, you can use DATE_FORMAT function.

for eg

MariaDB [d56cb677eaab3383]> SELECT DATE_FORMAT(SYSDATE(), ‘%Y-%m-%d’);
±-----------------------------------+
| DATE_FORMAT(SYSDATE(), ‘%Y-%m-%d’) |
±-----------------------------------+
| 2018-02-08 |
±-----------------------------------+
1 row in set (0.01 sec)

1 Like
3

Thank you @clarkej