SQL Server Output of data from the entire last month

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.

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:

calculate the months from a known datum (here we use zero) DATEDIFF(MONTH, 0, GETDATE())
add that number of months to zero, then deduct 1 month DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
add the same number of months to zero for the first day of the current month: 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:

-- T-SQL (SQL Server)
SELECT *
FROM your_table
WHERE timestamp_column >= DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
  AND timestamp_column < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

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 of getdate() 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:

select 
  current_timestamp "current timestamp"
, getdate()         "getdate"
, DATEDIFF(MONTH, 0, GETDATE()) "months from zero"
, DATEADD(MONTH, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) "day 1 last month"
, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) "day 1 this month"

current timestamp	getdate	months from zero	day 1 last month	day 1 this month
2023-07-22 01:07:00.753	2023-07-22 01:07:00.753	1482	2023-06-01 00:00:00.000	2023-07-01 00:00:00.000

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

SQL Server Output of data from the entire last month

1条答案

相关问题

热门标签

最新问答