3

How do I retrieve data in SQL from a previous date in the pass 12 months in a WHERE CLAUSE using the date and time functions?

--ServiceDates in past 12 months
WHERE YEAR(CONVERT(DATE, CLM_FROM_DT)) = 2016. 
3
  • Are you looking for one year from today fixed dates? Commented May 24, 2017 at 14:35
  • which datepart are you trying to filter?
    – McNets
    Commented May 24, 2017 at 14:37
  • Sorry I added the wrong logic. This is the logic. WHERE YEAR(CONVERT(DATE, CLM_FROM_DT)) = 2016. I am only going back 12 months from 3/31/2017 to 3/31/2016
    – Daddis2
    Commented May 24, 2017 at 15:25

2 Answers 2

4

Both of these should work depending on what you need.

For any date range:

WHERE CLM_FROM_DT BETWEEN '20160331' AND '20170331' 

If you are looking for 1 year back from current date:

WHERE CLM_FROM_DT>DATEADD(year,-1,GETDATE())

If you are looking for 1 year back from specific date (2017-3-31) as per your comment:

WHERE CLM_FROM_DT>DATEADD(year,-1, '20170331')
5
  • I am looking for 1 year back from 2017-3-31 instead of current date in which i don't think i need to use GETDATE().
    – Daddis2
    Commented May 24, 2017 at 15:35
  • Well you could simply use the dateadd function on that starting point if you wanted to.
    – Zane
    Commented May 24, 2017 at 15:57
  • Edited my answer. Commented May 24, 2017 at 16:27
  • WHERE CLM_FROM_DT BETWEEN '20160331' AND '20170331' will select one year + 1 day, if that column is a DATE. Commented May 24, 2017 at 20:15
  • @ypercubeᵀᴹ Good point. My answer was based on the dates in the question. Commented May 25, 2017 at 1:05
0
[...]
WHERE CLM_FROM_DT BETWEEN DATEADD(year,-1,GETDATE()) AND GETDATE()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.