Solved

SQL 2008 - help with date range

Posted on 2014-10-07
6
133 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - stored prodecure with execution plan make faster 24 115
sql query Help 12 54
affinity mask in sql server 1 23
email about the whoisactive result 7 30
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
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.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now