SQL 2008 - help with date range

HI,

   I have the following query:

select s.study_datetime
from dbo.patient AS p
INNER JOIN dbo.study AS s 
on s.patient_fk = p.pk
where s.study_datetime >= DATEADD(year, datediff(year, 1, getdate()), 0)
and s.study_datetime < DATEADD(year, datediff(year, -1, getdate()), 0)
order by s.study_datetime desc

Open in new window


this brings back the current year up to today's date.  

result for example - here is one row:  2014-10-07 10:02:28.000

I want to edit this query to find all of 2013 only. From 1/1/2013 - 12-31-2013.   Next I will need to do 2012 & so on, but I'm sure once I have it working for 2013, it will be easy to bring back 2012 & continue from there.   How would I edit this to bring back all of those rows?  Also, if someone could help explain how the 'DATEADD' & 'DATEDIFF' work together with in this example it would really help to shed some light on this logic for me.

thanks

EDIT:  updated code above - typo
doc_jayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
The "Where" clause becomes

where s.study_datetime >= '20130101'
and s.study_datetime < '20140101'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
If you use the date of the 1st January 2013 and cast it, it will use midnight on that date. For the second date, you make it less that 1st January 2014 which will include anything before the 1st January 2014
select s.study_datetime
from dbo.patient AS p
INNER JOIN dbo.study AS s 
on s.patient_fk = p.pk
where s.study_datetime >= cast('1 january 2013' as datetime)
and s.study_datetime < cast('1 january 2014' as datetime)
order by s.study_datetime desc

Open in new window

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
datediff(year, 1, getdate()) means get the number of years from the year 1900 plus 1 and now.

DATEADD(year, THEABOVE, 0) means add that number of years from the year 1900. It gets you to the start of the current year.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

doc_jayAuthor Commented:
too easy, I was under the assumption there would need to be a combo of the two functions together.  I'm sure that could be accomplished, but this is much more simple.

thanks
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you want a more complex version, no problem :-)

where s.study_datetime >= DATEADD(year, 113, 0)
and s.study_datetime <  DATEADD(year, 114, 0)

Open in new window

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you want to make your first bit easier to see, you could use:

cast(year(getdate()) as char(4)) + '0101'

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.