querying specifc dates that have expired

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)

querying specifc dates that have expired

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-14 : 08:44:59

Cedric writes "Ok, I have a date_expired field of the date that a license will expire. I would like to write a query for license's that have been expired for 200 plus days.

I don't know what it is but I'm missing and not getting the info that I want.

SELECT a.LNAME, a.FNAME, B.ID AS LIC_CONTACT_LINK_ID, C.LIC_NUMBER, C.LIC_TYPEDEF_ID, C.DATE_CREATED,
E.DATE_EXPIRES, D.LIC_STATUS, D.WORK_STATUS
--, DATEPART(DAY, getdate()) - DATEPART(DAY, E.DATE_EXPIRES) AS NO_OF_DAYS_LEFT

FROM C_CONTACT_PEOPLE A,
LIC_CONTACT_LINK B,
LIC_HOLDERINFO C,
LIC_STATUS D,
LIC_TERMDEF_HOLDERINFO_LINK E
WHERE a.ID = B.C_CONTACT_PEOPLE_ID
AND B.ID = C.PRIMARY_CONTACT_LINK_ID
AND C.ID = E.LIC_HOLDERINFO_ID
AND C.STATUS_ID = D.ID
and c.lic_typedef_id = 4
AND C.STATUS_ID IN (SELECT distinct id
FROM LIC_Status
WHERE (License_Status <> 'PENDING'))
--AND E.DATE_EXPIRES >= DATEDIFF (DAY, 200, getdate())
order by LNAME "

jones_d
Yak Posting Veteran

61 Posts

Posted - 2005-01-14 : 09:37:16

As in Books Online, the parameters for the DATEDIFF function are:

DATEDIFF ( datepart , startdate , enddate )

Try something like

WHERE DATEDIFF(DAY, startdate , enddate ) >= 200

vusil
Starting Member

22 Posts

Posted - 2005-01-14 : 09:41:22

Your syntax looks like you wanted to use the DateAdd function.

Try this code.

AND DATEDIFF (DAY,E.DATE_EXPIRES,getdate())>=200

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources