Petay87 Petay87 - 1 month ago 18
MySQL Question

How do I to label a column header as a formatted date?

What I have:



I have the following query that gets me pretty much what I need:

SET @sql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(Date = ''',
Date,
''', Description, NULL)) AS ',
CONCAT("'",Date,"'")
)
) INTO @sql
FROM updates;

SET @sql = CONCAT('SELECT Action, ', @sql, ' FROM updates GROUP BY Action');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


And gives the following output:

enter image description here

What I need:



What I would like is for the Column headers to be in the following format examples:


day/month/year

04/10/2016

%d/%m/&Y


What I have tried:



I have tried a number of different alterations to my code (far too many minor changes to list here) but each time I seem to get an error, to which I am guessing is regarding the formatting/syntax of the query.

Below is one example I tried to show the wave of thinking I was on:

SET @sql = NULL;

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(Date = ''',
Date,
''', Description, NULL)) AS ',
CONCAT("'",Date_Format(Date, "'"%d/%m/%Y"'"),"'")
)
) INTO @sql
FROM updates;


SET @sql = CONCAT('SELECT Action, ', @sql, ' FROM updates GROUP BY Action');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


As you can see I tried to set the "AS" value (Column name) using the DATE_FORMAT snippet of code. However, when I run this, I get the following error after the "FROM updates;" and before the "INTO @sql":


ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near '%m/%Y"'"),"'")


This in turn gives me the following error message after the PREPARE stmt FROM @sql:


ERROR 1064 (42000): You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax to use near 'NULL' at line 1


If anyone has any idea how to help me with this or point me in the right direction, I will be very grateful!

Answer

This select concat(date_format(now(), '%d/%m/%Y')) gives me 11/10/2016.

This is working perfectly fine. I guess you should consider removing the double-quotes. That should do the trick.