Dates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Dates

Author

Topic

sambrown18
Starting Member

14 Posts

Posted - 2008-04-18 : 12:51:06

I have an assignment using SQL this is the first time i have used it but i can understand the basics.

It is for an instrument service company and i need to find what instruments have not been serviced for a year.
i already know i have to:

SELECT instrument number, date of last service
FROM Instrument table

but after this i am unsure what code i can use in order to find all instruments that have not been servided in over 365 days what code can is best to use?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 12:53:13

Add a WHERE clause.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-04-18 : 13:04:25

try something like

select instrumentID
from instruments
where datediff(day,ServiceDate,Getdate()) > 365

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-18 : 13:22:17

Or if you want an index to be used and therefore be efficient, use this:

WHERE ServiceDate <= DATEADD(day, -365, GETDATE())

and a shortened version (as "day" is the default for date math):

WHERE ServiceDate < = GETDATE() - 365

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-19 : 03:04:36

If time matters, you can use

WHERE ServiceDate < dateadd(day,datediff(day,0,GETDATE() - 365),0)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources