dates passed as variables to stored procedure

[pmaxon]

This works:

SELECT *
FROM dbo.dhel_view_sample_test_results
WHERE (date_received >= '2006-04-13 00:00:00') AND (date_received < '2006-04-14 00:00:00') AND (lab_section = 'PS')

This Does not:
SELECT *
FROM dbo.dhel_view_sample_test_results
WHERE (date_received >= @date) AND (date_received < @date) AND (lab_section = 'PS')

@date = 04/14/2006

Why would this be? I need to fix it to accept the variable date. Using Sql Server.

TIA!

 

[unclerico]

WHERE date_received BETWEEN CAST(@date AS smalldatetime) AND CAST(@otherdate AS smalldatetime)

 

[mhoyt]

I wonder if you literally defined your variable as above?

SET @DATE = 4/14/2006

If so, I think your problem is that SQL does not recognize 4/14/06 as the equivilant of '2006-04-14'. I find that I have to make sure the SET command inlcudes the date in the later format. See below.

USE SANDBOX

CREATE TABLE TESTER (	
	PERSON VARCHAR(10)
	,BDAY DATETIME)

INSERT INTO TESTER VALUES ('ABE','2004-10-11')
INSERT INTO TESTER VALUES ('BETA','2004-9-15')
INSERT INTO TESTER VALUES ('CHARLIE','2004-11-15')

DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SET @STARTDATE = '2004-09-01'
SET @ENDDATE = '2004-09-30'

SELECT *
FROM TESTER
WHERE BDAY BETWEEN @STARTDATE AND @ENDDATE

 

[vongrunt]

Since date_received cannot be greater or equal (>=) and less than (<) something at once...

..
WHERE     (date_received >= @date) AND (date_received < @date[!]+1[/!]) AND (lab_section = 'PS')

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[SQLBill]

04/14/2006

Is that April 14, 2006 or is it a non-existant date - the 4th day of the 14th month in 2006? SQL Server doesn't know.

Either start your script (after the DECLAREs) with SET DATEFORMAT MDY or use the format '2006-04-14' which SQL Server cannot misinterpret.

-SQLBill

Posting advice: FAQ481-4875

 

[pmaxon]

Thanks for everyones input and discussions on this. Actually, it original query was this:

SELECT *
FROM dbo.dhel_view_sample_test_results
WHERE (date_received >= @date) AND (date_received < dateadd(day, 1, @date) AND (lab_section = 'PS')