The order in which dates are received between

Question

I have a SQL Server database containing a timesheet . This table is used to store roles for employees. Before performing the insert, which I check to see that the employee has previous roles on the selected days.

A procedure is stored here that returns the number of pre-existing roles.

 set @retVal=(select count(fk_RoleID) from dbo.TimesheetTable where (@startdate >= CAST(( STR( YEAR( StartDate ) ) + '/' + STR( MONTH( StartDate ) ) + '/' + STR( DAY( StartDate ) ) ) AS DATE ))-- AND EndDate <= '2012-08-30') and (@enddate < CAST( ( STR( YEAR( EndDate ) ) + '/' + STR( MONTH( EndDate ) ) + '/' + STR( DAY( EndDate ) ) ) AS DATE )) and fk_PersonnelID=@personnelID ) return @retVal 

The following are entries for one employee.

 pk_ID fk_PersonnelID fk_RoleID StartDate EndDate dateCreated 62 1 26 2012-10-01 2012-10-02 2012-10-25 15:55:12.940 81 1 20 2012-10-04 2012-10-06 2012-10-30 14:50:28.300 

If I try to do an insert where the start date is 2012-10-05 and the end date is 2012-10-11 , the request will not be able to capture startdate .. and the insert happens

What am I doing wrong?

Answer 1

The match error is incorrect. The test should be " both starts go to the opposite ends ." Remember, this is easy.

 SELECT @retVal = CASE WHEN EXISTS ( select * from dbo.TimesheetTable where StartDate <= @EndDate and @StartDate <= EndDate and fk_PersonnelID=@personnelID ) THEN 1 ELSE 0 END 

To test for existence, switch to using EXISTS, which will be short-circuited and give you the result as soon as the result is found, rather than COUNTing all matches.

Answer 2

You do not need to parse the StartDate and EndDate columns to get the desired results.

 SELECT @retVal = COUNT(fk_RoleID) FROM dbo.TimesheetTable AS tt WHERE @startDate <= tt.EndDate AND @endDate >= tt.StartDate AND fk_PersonnelId = @personnelID; return @retVal; 

@retVal here will return a number greater than 0 for inputs that already have a schedule in the database.

Answer 3

You have a logical error in your date comparison inequalities, because the date range ranges that I think you are trying to match are overlapping and not properly contained, as it seems, your code is supposed to be.

i.e. 10/4 - 10/6 strictly overlap 10/5 - 10/11, while your code means that the parameter interval must lie completely inside the data line s interval.