Jump to content

time as varchar check for overlapping times


Vincent_xyz

Recommended Posts

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

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.