CODEWITHSUNDEEP
phpmysqlsqlselect
user1493339
user1493339

Reputation: 429

How to select by using custom date

I have a custom date (dynamic build/input) as like '2013-5-13'
but my mysql database store as '2013-05-13'
so where i try to retrieve it out as like

SELECT * FROM TBL WHERE XDATE='2013-5-13' >>> it return me ZERO result

but

SELECT * FROM TBL WHERE XDATE='2013-05-13' >>> it return me CORRECT result

so how to solve this one directly in mysql statement?

Upvotes: 2

Views: 105

Answers (8)

Bora
Bora

Reputation: 10717

Use DATE_FORMAT

DATE_FORMAT(`XDATE`,'%Y-%c-%d')

Query

SELECT * FROM TBL WHERE DATE_FORMAT(`XDATE`, '%Y-%c-%d') = '2013-5-13'

MySql DATE_FORMAT Documentation

%c  Month, numeric (0..12)

Upvotes: 2

echo_Me
echo_Me

Reputation: 37253

use this %c in DATE_FORMAT

     select *  from TBL WHERE DATE_FORMAT(`XDATE`,'%Y-%c-%d') = '2013-5-13'

DEMO HERE

Upvotes: 6

TNguyen
TNguyen

Reputation: 176

You can format the date:

SELECT DATE_FORMAT('2013-5-13', '%Y-%m-%d');

in the WHERE part:

SELECT * FROM TBL WHERE XDATE=DATE_FORMAT('2013-5-13', '%Y-%m-%d')

MySQL docs

Upvotes: 0

Rajeev Ranjan
Rajeev Ranjan

Reputation: 4142

use this query :-

SELECT * FROM TBL WHERE UNIX_TIMESTAMP(XDATE)=strtotime('2013-5-13')

Upvotes: 0

NDM
NDM

Reputation: 6840

I think the cleanest way would be to use the MySQL DATE() function:

SELECT * FROM TABLE WHERE DATE(date_col) = DATE("2013-5-01")

MySQL docs

Upvotes: 0

Sushant Aryal
Sushant Aryal

Reputation: 3335

use strtotime function to convert both dates and then check.

Upvotes: 0

STT LCU
STT LCU

Reputation: 4330

This answer works only if your input is always in the format "year-month-day"

$date = '2013-5-13';
$parts = explode('-', $date);
$year = $parts[0];
$month = $parts[1];
$day = $parts[2];
//i assume that $year is always valid. Possible checks are left to the reader
//(lol i've always dreamed to write that sentence somewhere)
$month = str_pad($month, 2, "0", STR_PAD_RIGHT);
$day = str_pad($day, 2, "0", STR_PAD_RIGHT);

$clean_date = "$year-$month-$date"; //note the quotes

Now you can use $clean_date in your query.

Upvotes: 0

Kylie
Kylie

Reputation: 11749

Before querying the database just use strtotime() to set correctly...

 $date = "2013-5-16";
 $date = date("Y-m-d",strtotime($date));

 $sql = "SELECT * FROM TBL WHERE XDATE='".$date."'";

Upvotes: 1

Related Questions