I have column like below.
I need the date as column alias like
02/1/2019, 02/2/2019, 02/3/2019, 02/5/2019, 02/6/2019 etc
Asked
Active
Viewed 611 times
0
Strawberry
- 33,750
- 13
- 40
- 57
Raneesh Raveendran
- 57
- 5
-
4Please explain your issue using text and not images and clarify in what context you need this and what you have tried. For instance what type is t.date? – Joakim Danielson Mar 02 '19 at 10:30
-
T.date is stored as varchar? – P.Salmon Mar 02 '19 at 11:55
-
@JoakimDanielson t. date is varchar – Raneesh Raveendran Mar 03 '19 at 07:04
-
@P.Salmon t. date is varchar – Raneesh Raveendran Mar 03 '19 at 07:04
-
Possible duplicate of [mysql, transpose/pivot row to column, variable selects](https://stackoverflow.com/questions/14619678/mysql-transpose-pivot-row-to-column-variable-selects) – P.Salmon Mar 03 '19 at 08:07
2 Answers
0
You need to convert to date type first (STR_TO_DATE) and then back to string (DATE_FORMAT) in the wanted format
STR_TO_DATE(t.date, '%c/%e/%Y'), DATE_FORMAT(STR_TO_DATE(tdate, '%c/%e/%Y'), '%m/%e/%Y')
Joakim Danielson
- 43,251
- 5
- 22
- 52
0
SET @today = CURRENT_DATE(),
@day1 = DATE_ADD(CURRENT_DATE(),INTERVAL -1 DAY),
@day2 = DATE_ADD(CURRENT_DATE(),INTERVAL -2 DAY),
@day3 = DATE_ADD(CURRENT_DATE(),INTERVAL -3 DAY),
@day4 = DATE_ADD(CURRENT_DATE(),INTERVAL -4 DAY),
@day5 = DATE_ADD(CURRENT_DATE(),INTERVAL -5 DAY),
@sql = concat("SELECT JRR_RESULT, COUNT(LEFT(JRR_RESULT,40)) AS 'Total Error
Last
5 Days'"
", sum(IF( DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') = '" , @day1, "',1,0) ) AS
'",
@day1, "' "
", sum(IF( DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') = '" , @day2, "',1,0) ) AS
'",
@day2, "' "
", sum(IF( DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') = '" , @day3, "',1,0) ) AS
'",
@day3, "' "
", sum(IF( DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') = '" , @day4, "',1,0) ) AS
'",
@day4, "' "
", sum(IF( DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') = '" , @day5, "',1,0) ) AS
'",
@day5, "' "
" FROM your_table where DATE_FORMAT(CREATION_DATE,'%Y-%m-%d') >= '" , @day5,
"' "
" Group by LEFT(JRR_RESULT,40)");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
cci sugar
- 46
- 5
-
When answering an old question, your answer would be much more useful to other StackOverflow users if you included some context to explain how your answer helps. See: [How do I write a good answer](https://stackoverflow.com/help/how-to-answer). – David Buck Dec 04 '19 at 12:51