Date search add or omit past due

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)

Date search add or omit past due

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-09 : 12:39:03

I am searching for dates that are > a certain num of days.

Also, want to add OR omit the ones > today....

DECLARE @searchText INT
SET @searchText = 30
DECLARE @showPastDue INT
SET @showPastDue = 1 -- 1 = yes, show ones before today

DECLARE @searchDate DATETIME
SET @searchDate = DATEADD(day, @searchText, GETDATE())

SELECT.... blah ....
FROM.... blah

WHERE
a.cal = 1
AND @searchDate > a.calDueDate

This brings up all of them, but only want all if @showPastDue = 1.
But if @showPastDue = 0, I don't want all of them, just ones that have a date of today or later.

Suggestions?

Thanks,

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-09 : 12:46:55

SELECT *
FROM YourTable
WHERE DateTimeColumn >= DATEADD(DAY, DATEDIFF(DAY, 30, CURRENT_TIMESTAMP), 0) -- 30 days back
AND DateTimeColumn < DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 1) -- tomorrow

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 12:47:19

WHERE
a.cal = 1
AND ((@searchDate > a.calDueDate AND @showPastDue = 1)
OR (a.calDueDate >= getdate() AND @showPastDue = 0))

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-09 : 12:50:25

Excellent!

Thanks once again!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources