DATEDIFF Getting the previous month

I want to get the previous month relative to the current date

SELECT datediff(mm,-1开发者_JAVA技巧,2-2-2011)

This query gives 67 which is a wrong value .. where i went wrong ?

You can use DATEADD

eg.

SELECT DATEADD(month, -1, GETDATE())

This 2-2-2011 is 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.

Nor should you be using DATEDIFF - it gives you the difference between dates.

You should be using DATEADD to calculate new dates.

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:

SELECT DATEPART(mm, SELECT DATEADD(mm,-1, GETDATE()))

SELECT datepart(mm, dateadd(mm,-1,'2011/1/1') )

If you want the month before the current month, you want
SELECT MONTH(DATEADD(mm, -1, GETDATE()))

If you want the date for a month before the current date, you want
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.

You need to use DATEADD - not DATEDIFF

DATEDIFF calculates the difference between two dates - it doesn't add day or months to an existing date....

Also, you need to put your dates into single quotes: use '2-2-2011' instead of simply 2-2-2011.

And lastly: I would strongly recommend using the ISO-8601 date 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.

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 (your datetime type, your starting date,your ending date)

the function does (your ending date)-(your starting date)

in your case the below datediff will work just pefectly

SELECT DATEDIFF (month,[you_date_or_datetime_column],GETDATE()) = 1

You can use DATEADD try this code

For previous month

SELECT DATEADD(month, -1, GETDATE())

For 7 day previous

$date = date('Y-m-d',strtotime("-7 days"));
SELECT * FROM users WHERE `date` LIKE '%$date%'

$date varibale get previous date date