Calculate a Date based on another field... 1

[prover]

I have a table tblCase:
CaseID int
LocID int
CaseDate datetime
Allotted varchar

I need to add the CaseDate + Allotted and if the date equals today then return all the cases that are due.

here is the stored procedure (SQL) I'm working on:
SELECT *
FROM tblCase
WHERE dateadd(dd,***,CaseDate) = getdate()

I need a way to pass the "Allotted" value into the dateadd function or if there is an easier or better way....

HELP!!!!!

 

[dbtech]

Try this

Declare @Alloted int
SET @Alloted=10
SELECT *
FROM tblCase
WHERE dateadd(dd,@Alloted,CaseDate) = getdate()

If you want to eliminate the time part from the date field then you should use the query below.

SELECT *
FROM tblCase
WHERE
convert(datetime,convert(char(12),dateadd(dd,,@Alloted,CaseDate())))
=
convert(datetime,convert(char(12),getdate()))

did this help?

dbtech

 

[balor]

Why "Allotted = 10" in the previous example? Don’t you want to pass the value in the Allotted field from the table?

Just pass the value like "tblCase.Allotted".

Because Allotted is a varchar you need to convert the value to an int. You also need to convert the dates to get rid of the time (like in the previous example).

Example:

SELECT
*
FROM
tblCase
WHERE
convert(datetime,convert(char(12),dateadd(dd,convert(int,tblCase.Allotted),CaseDate)))
=
convert(datetime,convert(char(12),getdate()))

 

[dbtech]

you are right.
thanks for correcting my post.

dbtech