select statment to pull the nearest date rows

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)

select statment to pull the nearest date rows

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2006-07-26 : 16:58:04

I have the following select statement trying to pull the records for A.DueDate which is closest to getdate()

if i have 10 records in database for example and 3 records have the duedate of 072906 and 7 records has duedate of 080306

then it should get the nearest date as 072906 and pull all the records which has that duedate from table TAB_ccsNetActions

Please is that possible. I need to use this for a report and i am confused.

****************************************************************
SELECT S.SUNumber, S.SUTitle, A.Description,
FROM TAB_ccsNetSU AS S INNER JOIN TAB_ccsNetActions A ON S.SUID = A.ModuleRecordID
WHERE
AND isdate(A.Closeddate) = 0
And isdate(A.DueDate) > getdate()-- this is where need to be changed
GROUP BY S.SUNumber, S.SUTitle, A.description
*****************************************************************

Thank you very much for the information.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-26 : 18:04:11

Are the TAB_ccsNetActions.Closeddate and TAB_ccsNetActions.DueDate DATETIME, VARCHAR, NVARCHAR, or some other data type.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 05:12:36

If they are VARCHAR this kind of approach:

AND isdate(A.Closeddate) = 0
And CONVERT(datetime, A.DueDate) > getdate()

is toast IIRC - there is no guarantee that SQL will do the IsDate before the CONVERT.

I've had to do this in the past with a sub-select to get the PKs with Valid Dates, and then process the Date Range stuff in the outer query's WHERE clause :-(

Moral: Put dates in a DATETIME datatype, as you are no doubt hinting at MVJ

Kristen

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-27 : 05:33:13

select distinct dat.yourDate
from dat
where ABS(DATEDIFF(day, dat.yourDate, getdate()))=
(
select min(ABS(DATEDIFF(day, dat.yourDate, getdate())))
from dat
)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-27 : 07:17:49

quote:
Originally posted by Kristen
...Moral: Put dates in a DATETIME datatype, as you are no doubt hinting at MVJ ...

Actually, I was trying to figure out the reason of this piece of code:

And isdate(A.DueDate) > getdate()

It's obviously incorrect, but why is the ISDATE being used at all?

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources