How do I compare dates that are not dates without the minutes

[blue-genie]

i have 2 fields that are stored as varchars that are going into the db like this

 

2010-05-17 00:00:00

 

now i'm trying to do a select statement that says bring me back anything that is valid today.

 

so if startDate is 2010-05-17 00:00:00

and endDate is 2010-05-17 00:00:00

 

and now() is 2010-05-17 then it should be okay (not looking at time

 

$result = mysql_query("SELECT* from gamedetails where Date('startDate') < now() and Date('endDate') >= now() and gameIsActive = '1'") or die ('Error: '.mysql_error ());

 

 

[jdavidbakr]

  Quote

i have 2 fields that are stored as varchars that are going into the db like this

 

2010-05-17 00:00:00

 

now i'm trying to do a select statement that says bring me back anything that is valid today.

 

so if startDate is 2010-05-17 00:00:00

and endDate is 2010-05-17 00:00:00

 

and now() is 2010-05-17 then it should be okay (not looking at time

 

$result = mysql_query("SELECT* from gamedetails where Date('startDate') < now() and Date('endDate') >= now() and gameIsActive = '1'") or die ('Error: '.mysql_error ());

 

Use date():

 

if date(startDate) = date('2010-05-17') or date(now) = '2010-05-17'

 

etc.  That strips the time off of a date and returns just YYYY-MM-DD

[jdavidbakr]

Oh, I just noticed you have them as varchar.  Any reason why?

[blue-genie]

i don't know how to do databases properly and i had to put this together myself from scratch (i'm a flash developer) - so  I did what was the easiest for me at the time.

I'm trying to not change it at this point because i'm sure there will be alot of repercussions.

[Mchl]

There are likely to be more repercussions if you don't change it. You already store it in MySQL compatible format, so it is likely that no changes in application code will be necessary. All you need to do is to change datatype from VARCHAR to DATETIME. This will make all operations on dates much (MUCH) easier.

[blue-genie]

i've decided to do that. (do things properly)

have changed it to Date (not dateTime) and i've done this now which works but i'm sure is messy and laughable.

 

$today = getdate();

$y = $today[year];

$m = $today[mon];

$d = $today[mday];

$compDateStr = "".$y."-".$m."-".$d."";

 

$result = mysql_query("SELECT* from gamedetails where startDate <= date('".$compDateStr."') AND endDate >= date('".$compDateStr."') AND gameIsActive = 1");

 

[blue-genie]

i'll wait a few days and mark as solved in case you guys want to give me more feedback.

[Mchl]

Enter MySQL magic

 

SELECT
  * 
FROM
  gamedetails 
WHERE
  CURDATE() BETWEEN startDate  AND endDate
  AND gameIsActive = 1