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.
-
Are you looking for one year from today fixed dates?– SqlWorldWideMay 24, 2017 at 14:35
-
which datepart are you trying to filter?– McNetsMay 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– Daddis2May 24, 2017 at 15:25
Add a comment
|
2 Answers
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')
answered May 24, 2017 at 14:41
-
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().– Daddis2May 24, 2017 at 15:35
-
Well you could simply use the dateadd function on that starting point if you wanted to.– ZaneMay 24, 2017 at 15:57
-
-
WHERE CLM_FROM_DT BETWEEN '20160331' AND '20170331'will select one year + 1 day, if that column is aDATE. May 24, 2017 at 20:15 -
@ypercubeᵀᴹ Good point. My answer was based on the dates in the question. May 25, 2017 at 1:05
[...]
WHERE CLM_FROM_DT BETWEEN DATEADD(year,-1,GETDATE()) AND GETDATE()
