0

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.

Razzaf
  • 3
  • 1
  • 1
    Totally possible, but the way you write the logic in each RDBMS is different. Can you share what RDBMS you are using (Sql server, mysql, oracle, postgres, etc)? – JNevill Jul 20 '23 at 13:11
  • 1
    https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms – Reporter Jul 20 '23 at 13:13
  • 1
    For example, in a SQL Server DB, you will use MONTH for that. MONTH(GETDATE()) will return 7. Such a function exists in every RDBMS, with similar name and functionality. – Jonas Metzler Jul 20 '23 at 13:18
  • In SQL Server I would use just `datediff(month, ...)`. – Arvo Jul 20 '23 at 13:21
  • **PLEASE** add the tag that identifies which database you actually use. – Paul Maxwell Jul 21 '23 at 03:10
  • `dateadd(day, 1-day(getdate()), cast(getdate() as date))` Wrap that in `dateadd(month, -1, ...)` to get beginning prior month. – shawnt00 Aug 02 '23 at 00:41
  • Similar with: `datefromparts(year(getdate()), month(getdate()), 1)` – shawnt00 Aug 02 '23 at 00:43

1 Answers1

1

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:

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

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
  • first of all, thank you for the explanation. I will try these statements. But I still have one question. What is DATE_TRUNC ? Or rather what is the value I should/must put there ? – Razzaf Jul 21 '23 at 08:37
  • **Please** identify the database that you need to solution for. Is it Postgres? Date_truc just cuts the time of day from a timestamp eg 2023-07-21 09:23:57 becomes 2023-07-21 00:00:00 – Paul Maxwell Jul 21 '23 at 10:17
  • Looking at this again: If you use current_date instead of current_timestamp you don't need date_trunc. – Paul Maxwell Jul 21 '23 at 10:20
  • It is mssql. I hope this helps. I have added this as a tag. – Razzaf Jul 21 '23 at 13:04
  • Thanks for the tag. I have removed the unnecessary details for other databases to focus on SQL Server. Hopefully the expanded explanation targeting your dbms will make more sense. (e.g. now you don't need to use "date_trunc" as that is Postgres specific) & I do hope you now know how important the dbms tag is. – Paul Maxwell Jul 22 '23 at 01:16
  • Thanks for your help. I will check on this later this week because I am very busy at the moment. I will let you know the current status and ask queries if needed. – Razzaf Jul 25 '23 at 07:37
  • 1
    Thanks for your help. Your SQL Statement above helped me. After I modified it a bit, it suits my request. – Razzaf Aug 01 '23 at 14:32