MySQL - Convert INT to DATE format.

Question

How I can convert INT to date in php and than find this date in mysql table ?

Lets say I want to convert

$month = (int)$_GET['month'];
$month = 42014 ;//This means 04th month in 2014 year
$month = 04-2014; // I want this

And than find that in mysql table

$query=mysql_query("SELECT id FROM matches WHERE date=$month");// This need to select
                                                               // all data that is
                                                               // created in 04th month
                                                               // of 2014 year .



echo "Thanks :)";

For `42014` date value you can use `WHERE DATE_FORMAT(date,'%m%Y')='$month'` — M Khalid Junaid, Apr 21 '14 at 10:19

score 3 · Accepted Answer · edited May 23 '17 at 12:24

3

Don't pass $month parameter like you do.

Send and pass both $month and $year and handle them after:

$month = (int)$_GET['month'];
$year = (int)$_GET['year'];
if($month < 10) { // add this check for months lesser then october (they containt 1 digit, which is wrong for t-sql)
    $month = "0".$month;
}
$query=mysql_query("SELECT id FROM matches WHERE DATE_FORMAT(datefield, '%m-%Y') = '$month-$year'");

source

If you can't send both month and year in different variables, do this, like M Khalid Junaid suggested:

$query=mysql_query("SELECT id FROM matches WHERE DATE_FORMAT(datefield,'%m%Y')='$month'");

edited May 23 '17 at 12:24

Community

1
1

answered Apr 21 '14 at 10:20

Sharikov Vladislav

7,049
9
50
87

You are welcome. Sure, you can use M Khalid Junaid method, but I think better is to send both month and year. Choose what is better for you. – Sharikov Vladislav Apr 21 '14 at 10:33
I just made to send both separately but its not working with this query . This is how i done: SELECT date, home, away, pick, stake, odd, rezultat, moguci_dobitak, user, analiza FROM matches WHERE u_id='$id11' AND STR_TO_DATE(date, '%m-%Y') = '$month'-'$year' ORDER BY date DESC ... But its not working , when i remove "AND STR_TO_DATE(date, '%m-%Y') = '$month'-'$year'" its working but not selecting correct date range .. any help ? – MHB2011 Apr 21 '14 at 10:55
@MHB http://sqlfiddle.com/ create here your mysql scheme and give me a link. Or just add your sql scehem (t-sql query for creating table) to your question. – Sharikov Vladislav Apr 21 '14 at 10:56
I dont know how to create schema for my table (am novice) but however i uploaded matches table.sql here : http://www.speedyshare.com/bVaYM/matches-1.sql .. if this helps ? thanks – MHB2011 Apr 21 '14 at 11:13
Check this: http://sqlfiddle.com/#!2/ba0962/1 This is an example. Answer is updated. Hope, I helped you now. – Sharikov Vladislav Apr 21 '14 at 11:28
I made it , and its working ...I made it as @M Khalid Junaid said WHERE DATE_FORMAT(date,'%m%Y')='$month' .. Thanks everyone :) – MHB2011 Apr 21 '14 at 11:30
Well, I suggested same, but with two variables. It is better anyway, I think. – Sharikov Vladislav Apr 21 '14 at 13:53

score 2 · Answer 2 · answered Apr 21 '14 at 10:20

2

Try as below using DateTime::createFromFormat

$month = $_GET['month'];
$date = DateTime::createFromFormat('mY',$month);
echo $date->format('m-Y');

answered Apr 21 '14 at 10:20

Abhik Chakraborty

44,654
6
52
63

score 1 · Answer 3 · answered Apr 21 '14 at 10:20

1

Try this

$input=42014;
$year = strlen(substr($input,-4));
$month = substr(0,(strlen($input)-strlen($year)));
$final = $month."-".$year

answered Apr 21 '14 at 10:20

Sadikhasan

18,365
21
80
122

MySQL - Convert INT to DATE format.

3 Answers3