DATEADD - 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

DATEADD

Author

Topic

sampcuk
Starting Member

32 Posts

Posted - 2009-06-10 : 10:05:01

Hi,
I have the above script and basically I only want training events returned that ocurred 8 weeks ago. The script below is returning all the training events within the last 60 days (approx 8 weeks!) regardless of when...Can anyone help? Ive tried using the DATEADD function but no joy.
Thanks

select T.COURSE_REF, T.COURSENAME, T.STARTTIME, E.FORENAME, E.SURNAME, MGR.FORENAME, MGR.E_MAIL_ID as _MAILTO
from TRAINREC T, EMPLOYEE E, EMPLOYEE MGR
where T.EMPLOY_REF= E.EMPLOY_REF
and T.STATUS= 'COMPLETED'
and E.LOCATION = 'HQ'
and E.LEAVER NOT LIKE '1'
and E.MGR_REF *= MGR.EMPLOY_REF
and T.COURSE_REF = 'TTMHS03'
and (T.FINISHTIME between floor(convert(float, getdate()-60)) and
floor(convert(float, getdate())))

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-10 : 10:15:54

change this


and (T.FINISHTIME between floor(convert(float, getdate()-60)) and
floor(convert(float, getdate())))


and T.FINISHTIME >= dateadd(day, datediff(day, 0, getdate()), -60)  -- 60 days
and T.FINISHTIME <  dateadd(day, datediff(day, 0, getdate()),  1)   -- tomorrow's date

KH
[spoiler]Time is always against us[/spoiler]

sampcuk
Starting Member

32 Posts

Posted - 2009-06-10 : 10:18:16

Thank you.. However I am still getting those who attended within the last 60 days... i.e. someone who attended on Monday is being returned..

sampcuk
Starting Member

32 Posts

Posted - 2009-06-10 : 10:27:07

Its ok I have fixed it! Thanks you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources