Date Range query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Range query issue

Author

Topic

jrlanders
Starting Member

15 Posts

Posted - 2003-10-01 : 16:51:54

Hi All;

I have a date range query in a stored Proc that does not see to work. Any suggestions

Code:
Create PROCEDURE dt_SRdate
@Page int,
@RecsPerPage int,
@DateSchStart Datetime, //Format is 09/10/2003
@DateSchStop Datetime //Format is 09/10/2003

AS

Set NOCOUNT on

Create Table #TempItems
(
RecID int IDENTITY,
ID int,
FSRNum nvarchar(70),
ClientName nvarchar(70),
Reqr nvarchar(70),
ConsultantSch nvarchar(70),
DateSch datetime,
ReqrFirst nvarchar(70),
ReqrLast nvarchar(70),
MailAddr nvarchar(70),
Region int,
ProcessedFlag bit

)

Insert INTO #TempItems (ID,FSRNum,ClientName,Reqr,ConsultantSch,DateSch,ReqrFirst,ReqrLast,MailAddr,Region,ProcessedFlag)
Select ID,FSRNum,ClientName,Reqr,ConsultantSch,DateSch,ReqrFirst,ReqrLast,MailAddr,Region,ProcessedFlag FROM Presales
WHERE DateSch BETWEEN @DateSchStart and @DateSchStop

Order BY DateSch

Declare @FirstRec int, @LastRec int
Select @FirstRec = (@Page -1) * @RecsPerPage
Select @LastRec = (@Page * @RecsPerPage +1)

Select *,
MoreRecords =
(
Select COUNT (*)
FROM #TempItems TI
WHERE TI.RecID >= @LastRec
)
FROM #TempItems
WHERE RecID > @FirstRec AND RecID < @LastRec

Set NOCOUNT off

However, When I change the above where clause to the following it works perfectly:

WHERE DateSch BETWEEN '09/10/2003' and '09/12/2003'

I know the loaded Variables are getting into the procedure. When it runs with passed variables the only dates that finds are 09/12/2003. When I hard code the variables in the procedure it works fine

Ideas?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-01 : 17:51:39

First of all
@DateSchStart Datetime, //Format is 09/10/2003
@DateSchStop Datetime //Format is 09/10/2003

No it isn't - they are datetimes which are two numbers one for the date and one for the time.

Have you looked at what the dates are that are received?
If you are passing in a character string better to use yyyymmdd which is independent of the locale.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jrlanders
Starting Member

15 Posts

Posted - 2003-10-01 : 18:10:17

Hmmmm Good point.

before sending to the procedure I do a cDate() and convert it to a date string.

But I think your right about the dateTIME thing.

How do I make a conversion inside the Stored Procedure so that it is only a date? not date time?

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-01 : 18:59:43

If the problem is that you are getting a time as well as a date then
select @DateSchStart = convert(varchar(8),@DateSchStart,112)
if the problem is the date format then define it as a character string and
select @DateSchStart convert(datetime,@strDateSchStart,101) that's for mm/dd/yyyy - for dd/mm/yyyy 103

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources