Solved

SQL 2008 - help with date range

Posted on 2014-10-07
6
131 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

9 Experts available now in Live!

Get 1:1 Help Now