Help with SQL Statement

Hi,

Please can you help me with the following.

I am running an SQL statement and want to compare on the day and not the full date. What i have done is the following:

<?php
$date = date("d");
$sql = mysql_query("SELECT * FROM `customer_orders` WHERE type = '1' AND date = '$date'");
?>

PHP:

The date in the datebase is in the follow format e.g. yyyy-mm-dd i need a statement something like this:

$sql = mysql_query("SELECT * FROM `customer_orders` WHERE type = '1' AND date(m) = '$date'");

Code (markup):

Thanks in advance.
Adam

 

if field 'date' is a string with yyyy-mm-dd format, then you probably need to change only first line
$date = date("Y-m-d");

 

date("Y-m-d") will provide the format as Year-Month-Day

 

I dont want to match up the exact date, i want to check the day e.g. 15 and if today day is 15 also then a match is made.

 

hi Adam,

If you are using MySQL database, try using follwing stmt in your query:

day(str_to_date(OrdDt, '%m/%d/%Y' ) ) = '02'

use str_to_date() if date field is getting stored as string, otherwise no need to use it.

thanks,
Smruti.

 

Unfortunately that does not work, the date is stored in date format.

I thought this may work also but doesn't.

DAY(GETDATE(date)) = '02'

 

hi,

I hope you tried like this:

mysql_query("SELECT * FROM `customer_orders` WHERE type = '1' AND day(date) = '$date'");

 

the easiest thing you can do when comparing date and timestamps is to use/cinvert them to the unix timestamp


i cant post any links thus far but try to google unix timestamp and u will see lots of ways to use it

 

Why not just add something like this:

$date = explode("-", $sql["date"]);

Then you just echo $date[2] for the day part ($date[0] is the year, $date[1] is the month)

Then you can just compare the $date[2] with whatever value you want

 

yes,

definately you can use the explode function and store them in some variables..
and you can make on addition functions work according to ur wish..
example..

if you need the months in the format:
1,2,3,4,5,6,7,8,9
the make one function which removes the zero from the variables..i think the
after exploding date("Y-m-d"); ///YYYY-mm-dd//////
$date = date("Y-m-d");
$date = explode('-',$date);


$date[1] will have 01,02,03,04,05,06,07,08,09...

So you can make on function to removed the zeros or simply use a case switch to replace it..

I hope this will work..

 

And you could call that function... intval()!