time as varchar check for overlapping times

[Vincent_xyz]

Hello

 

I am writing a Timesheet application where I store the timesheet Time in and out as a varchar field  TimeIN: (7:00 AM) TimeOut: (5:00 PM)

 

I am having problems to check for overlapping times when a new timesheet has been added

 

I tried using str_to_date with no success:

$strSql="SELECT * FROM `TimesheetTbl` WHERE STR_TO_DATE(TimeSheetTimeIN, '%H:%i a') between 
STR_TO_DATE('$strSheetTimeIN', '%H:%i a') AND STR_TO_DATE('$strSheetTimeOut', '%H:%i a') OR 
STR_TO_DATE(TimeSheetTimeOut, '%H:%i a')  between STR_TO_DATE('$strSheetTimeIN', '%H:%i a') AND 
STR_TO_DATE('$strSheetTimeOut', '%H:%i a') AND `TimeSheetDate` = '$strTimeSheetDate' AND `TimeSheetUserID` = $EmpldbID";

 

  if the times would be saved as date or time field the query would look like:

 

$strSql=”SELECT * FROM `TimesheetTbl` WHERE `TimeSheetTimeIN` between ‘$strSheetTimeIN’ AND ‘$strSheetTimeOut’ OR `TimeSheetTimeOut`
between ‘$strSheetTimeIN’ AND ‘$strSheetTimeOut’ AND `TimeSheetDate` = ‘$strTimeSheetDate’ AND `TimeSheetUserID` = $EmpldbID”;

 

Can somebody please guide me in the right direction how to convert the varchar field in the sql query so I can check for overlapping times ...

 

thank you for your smarts and time

[Pikachu2000]

Store date and time information in the appropriate field type, such as DATETIME. Then you can use the many time and date functions that MySQL has built-in. Storing a time all by itself in a VARCHAR field is too ambiguous to have any real meaning.

 

Also, your editor is inserting curly quotes instead of the proper straight single quotes.

[Vincent_xyz]

the application is handling the string format everywhere else very well  I would like not to change the field type .. is it possible to check for overlapping times if I leave the field as varchar

[Pikachu2000]

It's impossible to know if the times overlap without a date.

[Vincent_xyz]

so i can not pull the times and convert the string times with strtotime() and check if there is an overlapping point and validate  ... there must be a way maybe not the easy way but possible?