Searching D@MN date ranges!!

Daddy

i have an online job application program that allows you to view applicants by the date submitted.

it works 100% when i am searching anywhere from january to september, but as soon as i try to search a month that is abbreviated by 2 digits (eg. october = 10, november = 11) it only pulls the applications submitted in january and nothing more.

here is the exact sql statement that is going to the database when i try to search for applicants between january and october:

SELECT * FROM table WHERE dateSubmitted >= '1/01/2003' AND dateSubmitted <= '10/01/2003' AND STATUS<>'Hired' ORDER BY lastname

anyone have any any explainations or help to offer??

steadyguy

MySQL dates usually take the format YYYY-MM-DD ... if the column that holds the date here is not set as type DATE but as a CHAR, TEXT, or something else, it will probably not compare them the way you expect: in a TEXT/CHAR comparison, 10 is greater than 1, but less than 2 ...

You should have no problems (except formatting the date properly) if you use a DATE type column.

maybl8r03

IMHO, date types in database are a huge concern for portability. Especially when the products we produce have to adhere to our client's db requirements not ours. I tend just to use the ISO date in a varchar or char datatype.

The standard date format of YYYYMMDD will sort quite happilly - using character sorting or numeric sorting.

To set it is simple. Simply do date("Ymd")

To retrieve it is slightly more complicated but it's worth the extra "magic" function - strtotime() :

$strDateFromDB = "20030929";
$strUserView = date("d M Y", strtotime($strDateFromDB));

Sxooter

Originally posted by maybl8r03
IMHO, date types in database are a huge concern for portability. Especially when the products we produce have to adhere to our client's db requirements not ours. I tend just to use the ISO date in a varchar or char datatype.

The standard date format of YYYYMMDD will sort quite happilly - using character sorting or numeric sorting.

To set it is simple. Simply do date("Ymd")

To retrieve it is slightly more complicated but it's worth the extra "magic" function - strtotime() :
$strDateFromDB = "20030929";
$strUserView = date("d M Y", strtotime($strDateFromDB));
[/B]

Sorry, but I'm gonna disagree here.

timestamp is a SQL 92 standard type. It's been around a long time, and every decent database I've ever played with has it as a type.

If you are forced to work with a database that doesn't understand what a timestamp type is, then that database vendor needs to get their stuff together and fix the underlying problem.

I've dealt with many different databases (Postgresql, Oracle, MSSQL, Informix, Ingres, db2) and they ALL understand date/time types quite well, and it's usually very easy to port back and forth. Other than MySQL insisting on calling an auto-timestamp column timestamp, and then making a new type called datetime (or whatever they called it) MySQL also works quite well with dates.

Further, storing your dates as text makes it very hard to make sure they are proper dates. One could easily enter the 30th of February or the 35th of December and the database would never stop them.

Also, if you need a constraint (i.e. the bill date must be later than the contract date etc...) then it's much more difficult to write such constraints if the date is stored as text, and such checking presents a higher load on the CPU.

date/time types, implemented properly, are a huge win for data consistency, and data consistency is the primary reason for using a database and not a flat file.