SQL query for today's date minus two months
If you are using SQL Server try this:
SELECT * FROM MyTable
WHERE MyDate < DATEADD(month, -2, GETDATE())
Based on your update it would be:
SELECT * FROM FB WHERE Dte < DATEADD(month, -2, GETDATE())
How to subtract 30 days from the current date using SQL Server
You can convert it to datetime
, and then use DATEADD(DAY, -30, date)
.
See here.
edit
I suspect many people are finding this question because they want to substract from current date (as is the title of the question, but not what OP intended). The comment of munyul below answers that question more specifically. Since comments are considered ethereal (may be deleted at any given point), I'll repeat it here:
DATEADD(DAY, -30, GETDATE())
SQL Select Records based on current date minus two days
you should try to use dateadd
function
select * from orders where orderdate > dateadd(dd,-1,cast(getdate() as date))
Now, this may be exactly what you need but then you need to understand that by casting to date we remove the time part and effectively go back to the start of the day and a day behind it(-1) gives the start of yesterday.
SQL Query Where Date = Today Minus 7 Days
declare @lastweek datetime
declare @now datetime
set @now = getdate()
set @lastweek = dateadd(day,-7,@now)
SELECT URLX, COUNT(URLx) AS Count
FROM ExternalHits
WHERE datex BETWEEN @lastweek AND @now
GROUP BY URLx
ORDER BY Count DESC;
How to subtract one month from a Date Column
The short answer: I suspect this is what you want:
dateadd(day, -datepart(day, Dated), Dated)
However, if you want "regular" subtract one month behavior in tandem with sticking to the end of month, having June 30 fall back to May 31 is slightly trickier. There's a discrepancy between the title or your question and the example where it appears you want the final day of month to stay anchored. It would be helpful for you to clarify this.
dateadd(month, -1, ...)
doesn't handle that when the previous month has more days than the starting month although it works the other way around. If that's truly what you need I think this should handle it:
case
when datediff(month, Dated, dateadd(day, 1, Dated)) = 1
then dateadd(day, -datepart(day, Dated), Dated)
else dateadd(month, -1, Dated)
end
There's also a flavor of several date functions in that expression and a sense of how this date stuff can get complicated. The condition in the when
looks to see if Dated
is the last day of the month by checking that the following day is in a different calendar month. If so we extract the day of month and subtract that many days to jump back to the last day of the previous month. (Months start at one not zero. So for example, counting backward 17 days from the 17th lands in the month before.) Otherwise it uses regular dateadd(month, -1, ...)
calculations to jump backward to the same day of month.
Of course if all your dates fall on the end of the month then this simple version will be adequate by itself because it always returns the last day of the previous month (regardless of where it falls in the starting month):
dateadd(day, -datepart(day, Dated), Dated) /* refer back to the top */
dateadd(day, -day(Dated), Dated) /* same thing */
And just for fun and practice with date expressions, another approach is that you could start on a known month with 31 days and calculate relative to that:
dateadd(month, datediff(month, '20151231', Dated) - 1, '20151231')
This finds the number of months between your date and a reference date. It works for all dates since it doesn't matter whether the difference is positive or negative. So then subtracting one from that difference and adding that many months to the reference point is the result you want.
People will come up with some pretty crazy stuff and I'm often amazed (for differing reasons) at some of the variations I see. chancrovsky's answer is a good example for closer examination:
dateadd(month, datediff(month, -1, Dated) - 1, -1)
It relies on the fact that date -1
, when treated as implicitly converted to datetime
, is the day before January 1, 1900, which does happen to be a month of 31 days as required. (Note that the - 1
in the middle is regular arithmetic and not a date value.) I think most people would advise you to be careful with that one as I'm not sure that it is guaranteed to be portable when Microsoft deprecates features in the future.
Subtracting months in sql correctly
One easy way is:
DECLARE @ReportingDate date = '6/30/2019'
SELECT CASE
WHEN @ReportingDate = EOMONTH(@ReportingDate) THEN EOMONTH(DATEADD(MONTH, -3,@reportingDate))
ELSE DATEADD(MONTH, -3,@reportingDate)
END
Subtract month and day mysql
using DATE_SUB
[docs]
like :
DATE_SUB((DATE_SUB(curdate(), INTERVAL 1 MONTH)), INTERVAL 4 DAY)
How to subtract 2 months and 18 days from a date
You can use the add_months()
function to subtract two months, and then simple date arithmetic to remove a further 18 days:
select add_months(<date_field>, -2) - 18
from <your_table>;
You can also use intervals:
select <date_field> - interval '2' month - interval '18' day
from <your table>;
SQL Fiddle.
But the interval approach can get an error if the final or intermediate dates do not exist; if you ran that with an initial date of 2014-04-30
then it would try to take two months off that first, which would give 2014-02-30
- which is not a valid date. Demo.
You can also combine them, with add_months
to subtract two months, and then use an intervals to subtract 18 days.
Related Topics
SQL Server Rounding Error, Giving Different Values
SQL Comma Delimted Column => to Rows Then Sum Totals
Split String into Several Rows
Anyway for Ado to Read Updated Data from a Read-Only Excel File Before Save? (Vba)
Does Inner Join Performance Depends on Order of Tables
Stop MySQL Reusing Auto_Increment Ids
SQL Server Regular Expressions
How to Deal with Single Quote in Word Vba SQL Query
How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2
Ora-30926: Unable to Get a Stable Set of Rows in the Source Tables When Merging Tables
Return a Grouped List with Occurrences Using Rails and Postgresql
It's Possible to Create a Rule in Preceding Rows in Sum
SQL Server Dynamic Pivot for an Unknow Number of Columns