Display records between two dates

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

Transact-SQL (2000)

Display records between two dates

Author

Topic

subduction35
Starting Member

7 Posts

Posted - 2008-01-08 : 04:30:20

Ive written a stored procedure to try and display records between two dates, reading the MSDN it looks correct but no records are coming back. Much appreciate any help

Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where (ChangeDate >= Convert(datetime, 12/06/2007) and ChangeDate <= Convert(datetime, 08/01/2008))
order by ChangeDate

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:33:12

try using ISO date format YYYYMMDD And you don't have use to convert to convert to datetime


where ChangeDate >= '20070612'
and   ChangeDate <= '20080108'

KH
[spoiler]Time is always against us[/spoiler]

subduction35
Starting Member

7 Posts

Posted - 2008-01-08 : 04:44:46

Many thanks for your help, I couldn't get this to work.

The SQL Statement is part of a store procedure, the dates are replaced with parameters @FromDate / @ToDate.

The first part of the statement uses the FromDate only, this works perfectly, it's only when I apply the ToDate that it now fails. The date in the database is saved as 12/12/2007

Many thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:48:52

quote:
I couldn't get this to work.

What is the error you are getting ?

what is the data type of the @FromDate and @ToDate ?

The ChangeDate column is a datetime datatype ?

KH
[spoiler]Time is always against us[/spoiler]

subduction35
Starting Member

7 Posts

Posted - 2008-01-08 : 04:54:10

If I put in the single quotes I get the error

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

If I remove the quotes I get the message

Arithmetic overflow error converting expression to data type datetime

The @FromDate and @ToDate are set to datetime, if I just use the @FromDate it works perfectly, it's when I use both to try and display the records in between

Many thanks for your help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 04:57:05

What is the data type for the column ChangeDate ?

Also can you post your query here (including the @FromDate, @ToDate) ?

KH
[spoiler]Time is always against us[/spoiler]

subduction35
Starting Member

7 Posts

Posted - 2008-01-08 : 05:02:39

The datatype for the field is datetime, below is the first part of my stored procedure.

CREATE PROCEDURE [spHistByUser] @UsName varchar(10), @Selection int, @FromDate datetime, @ToDate datetime

AS

if @Selection = 1
begin
if @ToDate is null and @UsName is not null
Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where @UsName = ChangedBy and (ChangeDate >= Convert(datetime, @FromDate))
order by ChangeDate
else
Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where @UsName = ChangedBy and (ChangeDate >= Convert(datetime, @FromDate)) and (ChangeDate <= Convert(datetime, @ToDate))
order by ChangeDate

if @ToDate is null and @UsName is null
Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where (ChangeDate >= Convert(datetime, @FromDate))
order by ChangeDate
else
Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where (ChangeDate >= Convert(datetime, @FromDate)) and (ChangeDate <= Convert(datetime, @ToDate))
order by ChangeDate

end

pwcphoto
Yak Posting Veteran

69 Posts

Posted - 2008-01-08 : 05:04:01

Wouldn't this work?

@FromDate VARCHAR(50)
@ToDate VARCHAR(50)
@ChangeDate VARCHAR(50)

WHERE @ChangeDate BETWEEN @FromDate AND @ToDate

Just make sure that the parameters you pass to the SP are enclosed in in a '

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

subduction35
Starting Member

7 Posts

Posted - 2008-01-08 : 05:21:12

Many thanks for your help, I just tried the following and it now seems to work correctly

Select hp.TblName, hp.FieldName, hp.OldValue, hp.NewValue, hp.PersDetID, hp.ChangeDate
from tbl_HistPers hp
where @UsName = Changedby and (ChangeDate between @FromDate and @ToDate)
order by ChangeDate

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 05:51:54

quote:
Originally posted by pwcphoto

Wouldn't this work?

@FromDate VARCHAR(50)
@ToDate VARCHAR(50)
@ChangeDate VARCHAR(50)

WHERE @ChangeDate BETWEEN @FromDate AND @ToDate

Just make sure that the parameters you pass to the SP are enclosed in in a '

Phil

-----------------------
I used to be a rocket scientist. Now I'm just a space cadet...

You dont need to use varchar datatype to store dates as DATETIME datatype is specifically for storing dates

Madhivanan

Failing to plan is Planning to fail

pleitch
Starting Member

8 Posts

Posted - 2008-01-09 : 22:28:57

I agree that at the very least the variables should be cast to datetime (or smalldatetime) at the point of comparison. Otherwise it will do a character based assessment. Check this out to see what I mean, only the very last statment (using cast) works.

select
case when '01/01/2008' between '31/01/2007' and '02/01/2008' then --Standard British datetime dd/mm/yyyy
'First Test Worked'
else 'First Test Failed' End as FirstTest,
case when '01/01/2008' between '01/31/2007' and '01/02/2008' then --Standard American datetime mm/dd/yyyy
'Second Test Worked'
else 'Second Test Failed' End as SecondTest,
case when '1 Jan 2008' between '31 Dec 2007' and '2 Jan 2008' then --Explicit
'Third Test Worked'
else 'Third Test Failed' End as ThirdTest,
case when cast('1 Jan 2008' as datetime) between cast('31 Dec 2007' as datetime) and cast('2 Jan 2008' as datetime) then --Dates
'Fourth Test Worked'
else 'Fourth Test Failed' End as FourthTest

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources