[Resolved] Searching for data bettween dates, simple help!

thehb

I need to search for data between two dates in SQL. The field is a VARCHAR field and not a DATE or DATETIME field.

REASON: I can't make it either (a DATE or DATETIME field) because the data is inserted from several different CSV files.

So far I've been using the following query, without trying to specify the dates it's working fine, but when I specify the dates I get nothing.

<?
$pass1 = mysql_query("SELECT sum(Duration) FROM Dailer_Records WHERE UsageTypeID=27 AND CallStartTime >= '5-3-2005' AND CallStartTime <= '5-4-2005' AND ServiceNumber='4697662343'");
while ($line = mysql_fetch_row($pass1)) {
 foreach($line as $key => $USAGE) {
$TOTALUSAGE= $USAGE/60;

}
}
?>

This is what the date (CallStartTime) looks like in the table:

5/3/2005 10:13:29 AM

Any help is appreciated . . especially since I'm supposed to have this done by tomorrow.

thehb

Anyone have any ideas ?

bradgrafelman

What version of MySQL server are you running?

EDIT: I still don't see why you can't use a DATE or DATETIME field...

bradgrafelman

Unless you're running 4.1.1 or later, I'm out of ideas. All the code I tried didn't work because MySQL doesn't recognize "5/3/2005 10:13:29 AM " as a valid date format.

thehb

Version: MySQL 3.23.58

I have no idea of what to do, it's almost my job if I cant figure it out though, hence that I'm still up at 2:00AM.

JUST TO CLARIFY: The reason I cant use a DATETIME field is because the data comes from another company in the form of a CSV file and I have a script setup via CRON to auto upload and insert the info into our database, so I cant really modify the format. With that said , MySQL wont let me make it a DATETIME field without formatting everything to the default 00/00/0000 . . . you know.

I have to say, it worked once the way I had it setup (above)which probally means it returned the incorrect information. I changed the dates (they should have been valid) and got nothing so I'm almost positive the first result was not correct .. I wonder why it returned anything at all though?

I'm in a hole. There has to be a solution, PHP(MySQL) does it all!, right ?

bough

You should be able to use the between command?

select * from table where date between ('2002/01/01')
AND ('2003/12/31');

I have the date stored in my tables in the format dd/mm/yyyy and use

select * from details where date between ('16/05/2005') and ('19/05/2005');

which works fine, with the date field being a varchar

bough

you can also search on date and time with between

ie select * from table where date between '16/05/2005 10:20:19' and '16/05/2005 10:20:23';

works fine

I hope that this helps

bradgrafelman

I thought the 'between' command only worked on DATE or DATETIME fields. Give that a shot, though.

Another solution would be to convert the date format in PHP, i.e. make the field in MySQL a datetime field and use:

$date = strtotime($dateFromCSVFile);
$date = date('Y-m-d G:i:s', $date);

Then insert the newly formatted $date into the database. This will allow you to use DATETIME fields, and then use syntax such as bough described.

As I said, I thought the 'BETWEEN' syntax only worked for date/datetime fields, though I've never messed with it. If it does, try using the code above (modifying it to fit the appropriate variables you used in your PHP script, of course).

Hope this reaches you in time! 🙁

bough

I am sure that the 'BETWEEN' command works on a variety of types.

I have just tried it on a test database and searched between tow values defined as varchar and it does work oky 🙂

you can also search between strings and integers with Between 🙂

it depneds which way you would prefer to have it, if you are okay with the date being stored as it is using between will work well, but you may wish to store the data as a datetime field for future use as bradgrafelman suggested :0

I am sure between this you should be able to resolve the problem

hope it goes well!

bradgrafelman

Ah, I see. I'm at work right now, and didn't have easy access to my MySQL server at home for testing. Thanks for the clarification.

If you need any elaboration/clarification on either of these methods, thehb, let us know!

thehb

I'm glad the first thing I did when I woke up was check the board, your suggestions really helped, thanks!

I decided to create another field for "Inserted Date", or the date the CSV file was updated because I really only needed to search by month anyway....that might be a little confusing . . but I have one more simple question:

OK, when I use this query like this it works fine:

$pass3 = mysql_query("SELECT sum(Duration) FROM Call_Records WHERE InsertedDate BETWEEN '2005-$month-01' AND '2005-$month-28' AND RatePeriod=2 AND ServiceNumber='2145296677'");

When I use it like this it dosent pick up the "$SuperServiceNumber" variable, everything else on the page does, anyone know why ?:

$pass3 = mysql_query("SELECT sum(Duration) FROM Call_Records WHERE InsertedDate BETWEEN '2005-$month-01' AND '2005-$month-28' AND RatePeriod=2 AND ServiceNumber='".$SuperServiceNumber."'");

thehb

Cancel that, I got it working!

Thanks everyone for your help!