0

I'm using the old wrong way for dates, and im unable to change it because it will take a lot of time and effort, not to mention a lot of crashes.

I have the following design:

  1. match_day : 31
  2. match_month : 12
  3. match_year : 2016
  4. time: 23:15

And comparing in the normal way ORDER BY (...etc) won't work here, and i see that i need to convert it to time, but the thing is these are INT not string, but i've tried to use the following query: 

SELECT match_day,match_month,match_year,time, STR_TO_DATE(match_day+'-'+match_month+'-'+match_year+'-'+time, '%e %c %Y %H:%i') AS date FROM SoccerMatches ORDER BY STR_TO_DATE(match_day+'-'+match_month+'-'+match_year+'-'+time, '%e-%c-%Y-%H:%i') DESC;

data is NULL, and i don't think it will work because these are INT and i guess we can't use '-' in queries.

this query:

SELECT match_day,match_month,match_year,time FROM SoccerMatches

result:

30 | 12 | 2016 | 23:15

Query im using and its showing false results:

  Select t.*,plan_b.value From SoccerMatches t, plan_b Order by (t.match_year AND t.match_month AND t.match_day) DESC;

Any ideas how to make it work / beside creating a new column for it, i want a workaround.

Jaeger
  • 1,646
  • 8
  • 27
  • 59

1 Answers1

1

Don't use + (is for sqlserver) in mysql you can use concat

SELECT 
 match_day
,match_month
,match_year
,time
, STR_TO_DATE(concat( match_day, '-', match_month ,'-',match_year,'-',time), 
    '%e %c %Y %H:%i') AS date 
FROM SoccerMatches ORDER BY date DESC;
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • `date` is still null in the result when i executed this query. – Jaeger Dec 30 '16 at 22:36
  • 1
    You have `-` between the parts of the date in `concat()`, but the format string has spaces. – Barmar Dec 30 '16 at 23:27
  • 1
    BTW, if you're concatenating a bunch of variables with the same separator, you can use `CONCAT_WS()` to give the separator once. – Barmar Dec 30 '16 at 23:28