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 you're comparing the column to a varchar in your query so I thought I'd ask.

 

If it's a datetime and your variables are both datetime, then I'd 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)

 

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

 

2. Change the @enddate to be the END of the date you're checking:

SET @endDate = '1/12/2007 11:59:59pm'

TimeSheets.Date BETWEEN @start and @endDate

 

-ner