SQL DATEDIFF 获取上个月
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4977375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DATEDIFF Getting the previous month
提问by Sudantha
I want to get the previous month relative to the current date
我想获得相对于当前日期的上个月
SELECT datediff(mm,-1,2-2-2011)
选择日期差异(mm,-1,2-2-2011)
This query gives 67which is a wrong value .. where i went wrong ?
这个查询给出了67这是一个错误的值..我哪里出错了？
回答by Pure.Krome
You can use DATEADD
您可以使用DATEADD
eg.
例如。
SELECT DATEADD(month, -1, GETDATE())
回答by Oded
This 2-2-2011is not a valid date literal - you are subtracting 2 from 2 and then 2011 from the result - proper date literals are '2-2-2011'and #2-2-2011#. You can use GETDATE()to get the current date, instead of relying on a literal.
这2-2-2011不是一个有效的日期文字 - 您从 2 中减去 2，然后从结果中减去 2011 - 正确的日期文字是'2-2-2011'和#2-2-2011#。您可以使用GETDATE()获取当前日期，而不是依赖文字。
Nor should you be using DATEDIFF- it gives you the differencebetween dates.
您也不应该使用DATEDIFF- 它为您提供日期之间的差异。
You should be using DATEADDto calculate new dates.
您应该DATEADD用于计算新日期。
Try this:
尝试这个：
SELECT DATEADD(mm,-1, GETDATE())
This will get the date a month ago.
这将获得一个月前的日期。
If you just want the month, you need to also use DATEPART:
如果您只想要月份，则还需要使用DATEPART：
SELECT DATEPART(mm, SELECT DATEADD(mm,-1, GETDATE()))
回答by Mitch Wheat
SELECT datepart(mm, dateadd(mm,-1,'2011/1/1') )
回答by Gabe
If you want the month before the current month, you wantSELECT MONTH(DATEADD(mm, -1, GETDATE()))
如果你想要当月之前的一个月，你想要SELECT MONTH(DATEADD(mm, -1, GETDATE()))
If you want the date for a month before the current date, you wantSELECT DATEADD(mm, -1, GETDATE())
如果您想要当前日期之前一个月的日期，您需要SELECT DATEADD(mm, -1, GETDATE())
BTW, SELECT datediff(mm,-1,2-2-2011)computes the number of months between day -1 and day -2011, which is 67 (2010 / 30). That's nowhere near what you seem to actually want.
顺便说一句，SELECT datediff(mm,-1,2-2-2011)计算第 -1 天和第 -2011 天之间的月数，即 67 (2010 / 30)。这与您似乎真正想要的相差甚远。
回答by marc_s
You need to use DATEADD- not DATEDIFF
您需要使用DATEADD- 不是DATEDIFF
DATEDIFFcalculates the differencebetween two dates - it doesn't add day or months to an existing date....
DATEDIFF计算两个日期之间的差异- 它不会将日期或月份添加到现有日期....
Also, you need to put your dates into single quotes: use '2-2-2011'instead of simply 2-2-2011.
此外，您需要将日期放入单引号中：使用'2-2-2011'而不是简单的2-2-2011.
And lastly: I would strongly recommend using the ISO-8601date format YYYYMMDD(here: 20110202) - it will work regardless of the language and regional settings on your SQL Server - your date format will BREAK on many servers due to language settings.
最后：我强烈建议使用ISO-8601日期格式YYYYMMDD（此处：）20110202- 无论 SQL Server 上的语言和区域设置如何，它都可以使用 - 由于语言设置，您的日期格式在许多服务器上都会中断。
回答by Mofor Emmanuel
DATEDIFF calculates the difference between your stating and ending dates every date previous to the current date has a positive number and every date next to the current date has negative number, this works in geting your specific date weather it a day,month,year or hour to understand this better below is the syntax of datediff
DATEDIFF 计算您的开始日期和结束日期之间的差异，当前日期之前的每个日期都有一个正数，当前日期旁边的每个日期都有一个负数，这可以让您的特定日期在一天、一个月、一年或一小时内度过为了更好地理解这一点，下面是 datediff 的语法
DATEDIFF (your datetime type, your starting date,your ending date)
the function does (your ending date)-(your starting date)
该功能确实 (your ending date)-(your starting date)
in your case the below datediff will work just pefectly
在你的情况下，下面的 datediff 将完美地工作
SELECT DATEDIFF (month,[you_date_or_datetime_column],GETDATE()) = 1
