Selecting a date Range

Question

question

technette asked Jan 17, '12

Selecting a date Range

I have a column with the following date format: 2002-03-25 00:00:00.000 In my query, I want to specify a date range for the past 3 years. How do I do that?

date-range

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-01-17T11:23:17Z

Grant Fritchey answered Jan 17, '12

Two ways immediately come to mind: ...WHERE MyDate > DATEADD(year,-3,GETDATE()) --or ...WHERE MyDate DATEADD(year,-3,GETDATE()) and BETWEEN GETDATE() Either will work. Probably the first is easier. The second allows for the idea of controlling the range more directly.

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey ♦♦ commented · Jan 17, 2012 at 01:00 PM

Fixed it so it looks right.

1 ·

technette commented · Jan 17, 2012 at 11:39 AM

Thank you for responding Grant! The between query doesn't return data... maybe because the current date may not work if there is not data in this table for the current date. The first query returns data but I'm still getting the following: 2049-12-31 00:00:00.000

0 ·

technette commented · Jan 17, 2012 at 12:14 PM

It worked with I used: DATEADD(year,+3,GETDATE()) Thank you!

0 ·

Martin Schoombee commented · Jan 17, 2012 at 12:48 PM

Using dateadd(year,+3,getdate()) is not correct...it will give you the data for the next 3 years and not the past 3 years. To get Grant's 2nd suggestion to work, switch the 2 predicates around. The "between" operator needs the lower limit to be first, i.e. ...between dateadd(year,-3,getdate()) and getdate()

0 ·

Grant Fritchey ♦♦ commented · Jan 17, 2012 at 12:59 PM

Ouch. Sorry about that. Thanks for the correction @Martin

0 ·

Show more comments

question