在SQL Server中查找去年的日期

I am using SQL Server 2008 and I am trying to calculate two dates from a year ago. Let’s say if today it is 3/18/13, I need to find 4/1/12 and also find 4/30/12.

我正在使用SQL Server 2008,我正在尝试计算一年前的两个日期。假设今天是3/18/13,我需要找到4/1/12并且还能找到4/30/12。

I tried the following which gives me 4/18/12, but not sure how to get the desired dates mentioned above. Also, I need to find 5/1/12, 5/31/12… etc. Any idea ? thanks.

我试过以下给我4/18/12,但不知道如何获得上面提到的所需日期。另外,我需要找到5/1 / 12,5 / 31/12 ......等等吗?谢谢。

DATEADD(MONTH,-11, GETDATE())    -  returns 4/18/12

I also tried

我也试过了

DATEADD(YEAR,-1, DATEADD(MONTH,DATEDIFF(MONTH,-11, GETDATE()), 0)) It returns  4/1/12 ,
DATEADD(YEAR,-1, DATEADD(MONTH,DATEDIFF(MONTH,-10, GETDATE()), 0)) also returns 4/1/12 instead of   5/1/12

1 个解决方案

#1

If what you want is the first and last day of the following month from one year ago today, something like this might work:

如果你想要的是一年前的下一个月的第一天和最后一天,那么这样的事情可能有用:

SELECT DATEADD(month,-11,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) )
SELECT DATEADD(month,-11,DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))))

I wouldn't be surprised if there is a more efficient way of doing this. The article at examples of how to Calculate Different SQL Server Dates discusses these kinds of calculations.

如果有更有效的方法,我不会感到惊讶。有关如何计算不同SQL Server日期的示例的文章讨论了这些类型的计算。

#1