how to return results where current date is before date stored field?

[spewn]

hello.

i have a field in my database called cDateEnd. in there, i store a date, such as 6/20/2009.

i want to return results on all entries where the cDateEnd is after todays date.

this is what i'm trying to accomplish:

$sth = $dbh->prepare("select cID from mytable where cDateEnd IS LATER THAN TODAYS DATE");

if i have to pass a value for todays date, it will be formatted as 1/21/2009.

any ideas would be helpful.

thanks.

- g

 

[whosrdaddy]

something like:

 SELECT cID 
 FROM mytable 
 WHERE TO_DAYS(cDateEnd) > TODAYS(CURRENT_DATE)

Cheers,
Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[spewn]

i actually came up with this, and it seems to work:

$thisDate='2009-01-31';
$sth = $dbh->prepare("select cID from mytable where trim(DATE(cDateEnd)) > '$thisDate'");

this is good form, right?

i really appreciate the help, and this site.

thanks!

- g

 

[whosrdaddy]

agreed,

one remark:
Mysql already knows what time it is, so it's cheaper to use the built-in function, but hey, that's up to you!


/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[whosrdaddy]

Spotted small error in the query, must be TO_DAYS() and not TODAYS()

SELECT cID 
 FROM mytable 
 WHERE TO_DAYS(cDateEnd) > TO_DAYS(CURRENT_DATE)

Now where is that darn Edit button?



/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[guelphdad]

why use the to_days function which is an unnecessary calculation. why not just compare the two dates?

WHERE cDateEnd > current_date()

 

[whosrdaddy]

yeah, correct indeed.

in fact I work a lot with timestamps which include the time part and then the TO_DAYS function is necessary to compare dates.

Cheers,
/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[r937]

you make it sound like you are forced to use TO_DAYS, but you aren't

whenever you apply a function to a column, the database optimizer cannot use an index on that column, so in many cases you end up with a table scan

the "best practice" method for choosing timestamp values for specific dates involves using an open-ended upper bound in a range test

for example, to get all rows for yesterday...

WHERE cDateEnd >= CURRENT_DATE - INTERVAL 1 DAY
  AND cDateEnd  < CURRENT_DATE

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon