Problem with date and time function

I am using MSSQL 2000. I building a search page in which all jobs listed since the past seven days will be selected sequentially. The dropdownlist goes from 0-7, if i select 1 it should give yestedays jobs , if i select 2 it should give all the jobs within two days and so on upto seven days. I am using this Sql Statement but its not working out. Has anyone got any idea about this problem?
SELECT * FROM TableName WHERE ColumnName BETWEEN DATEADD(dd,-1, GETDATE( )) AND GETDATE( )
Thanx in AdvanceYou probably want a stored proc that would look something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, DATEADD(day, @.Days + 1, CONVERT(VARCHAR(12), GETDATE())))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||'Cept for that I didn't read your post carefully enough. To getthe jobs from all previous days, instead of a single selected day, it'dlook something like this:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12), GETDATE()))
SET @.EndDate = DATEADD(second, -1, CONVERT(VARCHAR(12), GETDATE()))

SELECT * FROM TableName WHERE ColumnName BETWEEN @.StartDate AND @.EndDate

|||Xander, I suggest that this might work better:

CREATE PROCEDURE GetJobs
(
@.Days INT -- Days before the current date as a positive integer.
)
AS
SET @.Days = @.Days * -1
DECLARE @.Now
DECLARE @.StartDate DATETIME
DECLARE @.EndDate DATETIME
SELECT @.Now = GETDATE()
SET @.StartDate = DATEADD(day, @.Days, CONVERT(VARCHAR(12),@.Now))
SET @.EndDate = CONVERT(char(8), @.Now,112)

SELECT * FROM TableName WHERE ColumnName >= @.StartDate AND ColumnName < @.EndDate

What you suggested could give unexpected results if it is a fewmilliseconds before midnight. What I have done takes millisecondsout of the equation. Also, it's good to take a snapshot of thecurrent time in the beginning and use that static date/time for theremainder of the procedure. This way you don't need to worryabout the stored procedure crossing date boundaries in the middle ofexecution.
|||Thank you very much for all your replies.
I tried the above idea and worked perfectly.
The problem which was bugging me finally got solved.
Thanx once again