Home » SQL & PL/SQL » SQL & PL/SQL » previous month (oracle)
previous month [message #689831] Mon, 13 May 2024 19:48 Go to next message
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:
I’m worried about the number 33  used to get the first day of previous month. Can you please help me with some generic function.
Thank you.

Regards
suji
Re: previous month [message #689832 is a reply to message #689831] Mon, 13 May 2024 20:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> -- optional SQL*Plus column format commands:
SCOTT@orcl_12.1.0.2.0> COLUMN "First Day of Previous Month"  FORMAT A27
SCOTT@orcl_12.1.0.2.0> COLUMN "Last Day of Previous Month"   FORMAT A26
SCOTT@orcl_12.1.0.2.0> -- query:
SCOTT@orcl_12.1.0.2.0> SELECT TRUNC (ADD_MONTHS (SYSDATE, -1), 'MONTH') AS "First Day of Previous Month",
  2  	    TRUNC (SYSDATE, 'MONTH') -1 	      AS "Last Day of Previous Month"
  3  FROM   DUAL
  4  /

First Day of Previous Month Last Day of Previous Month
--------------------------- --------------------------
Mon 01-Apr-2024             Tue 30-Apr-2024

1 row selected.
Re: previous month [message #689833 is a reply to message #689832] Mon, 13 May 2024 20:48 Go to previous messageGo to next message
Sekhar6617
Messages: 24
Registered: March 2021
Junior Member
Thank you.
Re: previous month [message #689835 is a reply to message #689833] Tue, 14 May 2024 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
cookiemonster
Messages: 13925
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

you can't use magic numbers for months since they vary in length (same for years).
Re: previous month [message #689851 is a reply to message #689836] Mon, 20 May 2024 22:16 Go to previous message
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.
Previous Topic: Get the MetaData for FGA Policy
Next Topic: More precision about log errors
Goto Forum:
  


Current Time: Sat Jun 15 15:53:37 CDT 2024