| previous month [message #689831] |
Mon, 13 May 2024 19:48  |
 |
Sekhar6617
Messages: 24
Registered: March 2021
|
Junior Member |
|
|
Hi Everyone,
Can you please help me with sql to get the 1st day of previous month and last day of previous month.
I tried with below sql:
SELECT trunc (SYSDATE-33, ‘mm’) as “ First Day of Previous Month”,
trunc(SYSDATE, ‘MONTH’)-1 as “Last Day of Previous Month” from dual:
Thank you.
Regards
suji
|
|
|
|
|
|
|
|
| Re: previous month [message #689835 is a reply to message #689833] |
Tue, 14 May 2024 00:27  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Another way:
SQL> SELECT TRUNC (ADD_MONTHS (SYSDATE, -1), 'MONTH') AS "First Day of Previous Month",
2 LAST_DAY (ADD_MONTHS (SYSDATE, -1)) AS "Last Day of Previous Month"
3 FROM DUAL
4 /
First Day o Last Day of
First Day of Previous Month Last Day of Previous Month
--------------------------- --------------------------
01-APR-2024 30-APR-2024 [Updated on: Tue, 14 May 2024 00:33] Report message to a moderator |
|
|
|
| Re: previous month [message #689836 is a reply to message #689835] |
Tue, 14 May 2024 04:05 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Also, stop worrying about 33 and realize that it definitely won't work if you're running your code at the start of a month. And if it's march you'll get the wrong result on the 5th (or 4th if it's a leap year).
SQL> SELECT trunc (to_date('05-MAR-2022', 'DD-MON-YYYY')-33, 'mm') as first,
2 trunc(to_date('05-MAR-2022', 'DD-MON-YYYY'), 'MONTH')-1 as Last from dual;
FIRST LAST
----------- -----------
01/01/2022 28/02/2022
|
|
|
|
| Re: previous month [message #689851 is a reply to message #689836] |
Mon, 20 May 2024 22:16 |
 |
mathguy
Messages: 108
Registered: January 2023
|
Senior Member |
|
|
What do you mean by "day"? This may be relevant to how you plan to use the answer(s) to your question.
Let me explain. A "day" is a time interval, lasting 24 hours. All the queries shown so far (and many others that could be used) don't produce a "day" in this sense; they only produce a point-in-time, such as 1 April 2024 at 00:00:00 (midnight) or 30 April 2024 at 23:38:53 (close to the end of that day).
There are differences between the answers you got so far: Barbara's answer for the last day of the month will produce that day with a time-of-day of 00:00:00, but Michel's answer will have whatever time-of-day it is when you run the query; the date will be correct, but the time-of-day will be pretty random. This is because both ADD_MONTHS and LAST_DAY only work on the "date" component of a date-time, but they both preserve the time-of-day of the input. By contrast, TRUNC truncates to 00:00:00.
If all you need is a graphical representation of the result, in the format dd-Mon-yyyy or some other format that only shows the date but not the time-of-day, then the solutions are equivalent.
But, in most cases, when people ask about finding the first and last day of the previous month, they want to run a query where they only consider data "for the last month". Let's say there is a TRANSACTION_DATE column, and it is a date-time, where the time-of-day is not necessarily 00:00:00. Then using either of the solutions proposed by Barbara and Michel won't be appropriate. You will leave out some transactions that happen on the last day of the month: at any time other than 00:00:00 in Barbara's solution, and at any time later than the "current time" (SYSDATE) in Michel's answer.
If that is how you need to use this, then the correct approach is to look for TRANSACTION_DATE greater than or equal to the first day of the previous month at 00:00:00, and strictly less than THE FIRST DAY OF THE CURRENT MONTH at 00:00:00. You don't need to worry about the last day of the previous month (and actually doing so will likely give wrong results).
So - depending on how you actually need to use the results - you may need to use the ideas in the answers given so far, but not the exact answers as shown.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
