Solved

SQL 2008 - help with date range

Posted on 2014-10-07
6
137 Views
Last Modified: 2014-10-07
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
0
Comment
Question by:doc_jay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40366117
The "Where" clause becomes

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

Open in new window

0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 40366121
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

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40366126
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:doc_jay
ID: 40366132
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40366137
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

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40366145
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

0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question