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.
How to subtract one to the month of a date format in mysql?
Your solution can be derived from: Subtract month and day mysql
e.g. (DATE_SUB(curdate(), INTERVAL 1 MONTH)
SELECT COUNT(*), wedding, DATE_SUB(weddate,INTERVAL 1 MONTH) FROM weddingtable
Subtract months to a date using Spark Scala
You can use add_months
with negative months value as below
val dfMonth = df1.withColumn("Result", add_months(
to_date(col("Date"), "dd/MM/yyyy"), col("MonthSub") * lit(-1))
)
dfMonth.show(false)
Output:
+----------+--------+----------+
|Date |MonthSub|Result |
+----------+--------+----------+
|30/11/2020|12 |2019-11-30|
|25/07/2020|5 |2020-02-25|
|11/01/2020|1 |2019-12-11|
+----------+--------+----------+
You can change the date format as you like.
Subtract 1 month from datetime column from database
Use MONTH
function to extract the month from the datetime (probably visit_date_time
in your case) and subtract 1 from it.
SELECT
COUNT(ip_address) AS count,
DATE(visit_date_time) AS visit_date_time,
MONTH(DATE(visit_date_time)) - 1 mon
FROM
db_views
WHERE
user_id = $id
GROUP BY DATE(visit_date_time)
ORDER BY visit_date_time
LIMIT 30
How to add/subtract an array of varying Month timedeltas in Pandas?
you could try
df['new_timestamp'] = df.timestamp - pd.to_timedelta(df.delta_in_months,unit='M')
df['new_timestamp'] = df['new_timestamp'].dt.date
One important thing is to remember that 1 'M' = 30 'D'.
Month subtract month
You can convert the date to month period and then do the subtraction:
date1.to_period("M") - date2.to_period("M")
# 7
Related Topics
Detect If Value Is Number in MySQL
How to Find What Privileges a Group Has in Redshift
Oracle Sql, Concatenate Multiple Columns + Add Text
Sql Server Query to Find All Permissions/Access for All Users in a Database
Mysql - Operand Should Contain 1 Column(S)
Sql to Find Upper Case Words from a Column
Get Count of Records in Every Hour in the Last 24 Hour
Mysql Left Join With Limit 1 Not Returning Desired Result
Daily Report by Date With Mssql for Mutiple Column
How to Change Column Order in a Table Using SQL Query in SQL Server 2005
Select Only Rows That Contain Only Alphanumeric Characters in MySQL
Select and Compare Two Datetime Columns from Different Table Without Having Any Relation
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Convert String to Date in Ms Access Query
Convert Julian Date to Yyyy-Mm-Dd
How to Strip All Non-Alphabetic Characters from String in SQL Server