SQL Date/Time sorting question

[ThunderAI]

I need some help figuring out how you would limit an SQL search result list to only those items that occurred prior to a different date and time.

 

For example, assume you do an inspection looking for problems/discrepancies and you record those discrepancies into the database and I want to find all discrepancies that were repaired prior to a specific date and time.

 

I was thinking this: $sql = "SELECT * FROM tbl_139_327_sub_d_r WHERE discrepancy_repaired_inspection_id = '".$discrepancy_id."' AND discrepancy_repaired_date <= '".$tempinspdate."' AND discrepancy_repaired_time <= '".$tempinsptime."' ORDER BY discrepancy_repaired_date, discrepancy_repaired_time";

 

 

Would this work, or would I end up missing some results like if something was fixed on a previous day, but it happened to be after the given time thus not being returned. How would I do this querry?

[fenway]

Don't separate date & time fields like that -- otherwise, you'll have to merge then back together for your where clause.

[ThunderAI]

How do you suggest I do it then?

 

If I have a Date field and a time field and need to find entries before a certain date and time, I can force one side back together by a stretotime function to make a timestamp, but I can't do that in the SQL expression, or can i?

 

For example; I could have the inspection date and time combined prior to executing the SQL, but the discrepancy would still be in date and time.  So you would need an SQL statement that created a new column called datetime or something to sort the discrepancy.

 

Unless what your saying is to go back to the actual strucutre design and change everything to a timestamp and have the script combine the user input into one datetime filed that is a timestamp.

[Pikachu2000]

It's kind of clunky to look at, but some form of this query would work. Your best bet is still to store the date and time together in a DATETIME field, though.

 

SELECT field1, field2 FROM table WHERE CONCAT_WS(' ', `date_field`, `time_field`) < DATE_SUB( NOW(), 7 DAY )

[fenway]

Unless what your saying is to go back to the actual strucutre design and change everything to a timestamp and have the script combine the user input into one datetime filed that is a timestamp.

That's exactly what I'm saying.