Briefly the explanation of my problem:
We use an inventory tool in our company, which should automatically output all inventory movements of the last month at the beginning of each month (i.e. on the 1st). Here I can control via a filter on the date, which data of a table should be output. Currently my filter is as follows: "> CURRENT_TIMESTAMP - 31". So all inventory movements of the last 31 days until today are displayed. However, this is unfortunately only 95% correct, because if I have, for example, a month with only 30 days or as in February with even only 28 days, I always take the last days of the month before also. Now to my question.
Is it possible to set a filter or a SQL statement that will always output me only the inventory movements where the month is a month before the current month ? So for example the output takes place on 08/01/2023, then I would like to get all inventory movements from the previous month so July (07/01/2023 - 07/31/2023 output.
Unfortunately I am a newbie in SQL, so it is hard for me to find a solution for this.
1条答案
按热度按时间qncylg1j1#
In SQL Server current_timestamp returns a
datetime
value such as 2023-07-22 07:53:41.270. This function is the ANSI SQL equivalent to the SQL Server specific GETDATE() function.A way determine that start of "last month" from current_timestamp, or getdate(), is as follows:
DATEDIFF(MONTH, 0, GETDATE())
DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
So we can now filter for data that is ">= day 1 of last month" and "< day 1 of this month" e.g:
This where clause will dynamically filter for any data falling within the prior month regardless of date/time precision of the column used i.e. it will work for date, smalldatetime, datetime and datetime2
nb: If you prefer you can use
current_timestamp
instead ofgetdate()
but I believe you will find most information about similar needs still uses the SQL Server specific getdate() function - so I have followed suit.To further understand this date manipulations try this query:
fiddle
Final word. Do not be tempted to use "between" when filtering for a date range such as "last month". The problem with "between" in SQL is that it INCLUDES both the start point and the end point of the range - and this potentially leads to double accounting for any data that is ON a border. It is far safer to use the combination of
>=
with<
as shown earlier. see