doc_jay
asked on
SQL 2008 - help with date range
HI,
I have the following query:
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
DATEADD(year, THEABOVE, 0) means add that number of years from the year 1900. It gets you to the start of the current year.
ASKER
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
thanks
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)
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