Convert integer to date in mysql ,php

Question

I have 2 columns one having month and another year , I want to get all the records greater than that month and year

I cannot use date functions as those columns are of integer type, It gives incorrect results

How can I get records greater than that month and year

For example How can I get events which took place after february 2017

http://stackoverflow.com/questions/20617100/php-converting-integer-to-date-reverse-of-strtotime — fizzi, Feb 09 '17 at 09:26
@Harsh Barach TRIED select *, STR_TO_DATE(CONCAT('joining_month','joining_year'),'%m,%Y') AS entry_date from event WHERE entry_date >'2-2016' But entry_date is NULL — san, Feb 09 '17 at 09:36

Jan Rydrych · Accepted Answer · 2017-02-09T09:36:58.143

3

What about this approach:

SELECT * FROM table WHERE CONCAT(year, month) > 201702

edited Feb 09 '17 at 09:36

answered Feb 09 '17 at 09:31

Jan Rydrych

Piotr Pasich · Answer 2 · 2017-02-09T09:41:44.713

-1

WHERE CONCAT(year, month) > 201702

probably you have also some php code inside so:

$date = strtotime('2018-01-01 01:01:01');
$sql =  'SELECT * FROM YOUR_TABLE 
          WHERE CONCAT(year, month) > ' . date('Ym', $date);

edited Feb 09 '17 at 09:41

answered Feb 09 '17 at 09:30

Piotr Pasich

And what if the date is, for example, january 2018? – Jan Rydrych Feb 09 '17 at 09:36
You didn't understand correctly - if I want everything with date greater than 02/2017, but in the DB is record with the month 01, year 2018. Your code won't select it... – Jan Rydrych Feb 09 '17 at 09:39

Maciej__ · Answer 3 · 2017-02-09T09:52:46.263

-1

SELECT * FROM events WHERE CONCAT(year,month) >= 201702

or without CONCAT

SELECT * FROM events WHERE( month > 2 AND year =2017) OR (year > 2017)

edited Feb 09 '17 at 09:52

answered Feb 09 '17 at 09:30

Maciej__

And what if the date in the DB record is, for example, month 01, year 2018? – Jan Rydrych Feb 09 '17 at 09:42

3 Answers3