Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

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
0
doc_jay
Asked:
doc_jay
  • 4
1 Solution
 
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

0
 
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

0
 
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.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
0
 
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

0
 
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

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now