Experiencing a problem with a where statement with dates.

[mike55]

Here is my stored procedure:

CREATE PROCEDURE dbo.IndividualByMonth

	@start datetime,
	@finish datetime,
	@staff nvarchar

AS

	DECLARE @EIDs as int

	SELECT @EIDs = EmployeeId
	FROM StaffList
	WHERE Username = @staff


	SELECT TOP 100 PERCENT dbo.Timesheets.Id, dbo.Timesheets.Employee, dbo.Timesheets.[Date], 
			dbo.Timesheets.Parent, dbo.Timesheets.Child,  dbo.Timesheets.Hours, dbo.Timesheets.Comments, 
				SUM(dbo.Timesheets.[Hour]) AS [hour], SUM(dbo.Timesheets.[Minute]) AS [minute]
	FROM dbo.Timesheets INNER JOIN
	                      dbo.StaffList ON dbo.Timesheets.Employee = dbo.StaffList.EmployeeId
	WHERE (dbo.Timesheets.Employee = @EIDs) AND (dbo.Timesheets.[Date] BETWEEN CONVERT(DATETIME, @start,103) AND CONVERT(DATETIME, 
                      @finish,103))
	GROUP BY dbo.StaffList.Surname, dbo.StaffList.Forename, dbo.Timesheets.Id, dbo.Timesheets.Parent, dbo.Timesheets.Child, dbo.Timesheets.Hours, 
	                      dbo.Timesheets.Comments, dbo.Timesheets.OffSite, dbo.Timesheets.Employee, dbo.Timesheets.[Date]
	ORDER BY dbo.StaffList.Surname, dbo.StaffList.Forename

GO

If I take the 2nd select statement, and fill in 1 for the variable @EIDs and 01/11/2007 for @start and 01/12/2007 for @finish and place it in a view I get back the rows that I am looking for.

However, if I simply pass in the above two dates I cannot get back any data.

Any suggestions?

Mike55.

 

[Nerseus]

What DataType is column TimeSheets.Date? Silly question, I know... but youre comparing the column to a varchar in your query so I thought Id ask.

If its a datetime and your variables are both datetime, then Id think it would work just fine. Keep in mind that datetime DOES have a time portion so if your table values have time stored, then your "BETWEEN" will likely not include records on the @enddate date.

For example:
Row 0: Date = 01/11/2007 1:32pm
Row 1: Date = 01/12/2007 2:32pm

If @start = 1/11/2007 and @end = 1/12/2007 then it will NOT pick up Row 1.

The two common fixes are:
1. Change the query to not use BETWEEN, but use something like:
TimeSheets.Date >= @start and TimeSheets.Date < (@enddate + 1 day)

Youll use something like DATEADD(d, 1, @enddate) for that last bit.

2. Change the @enddate to be the END of the date youre checking:
SET @endDate = 1/12/2007 11:59:59pm
TimeSheets.Date BETWEEN @start and @endDate

-ner