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: 9095
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: 68653
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 message
cookiemonster
Messages: 13923
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).
Previous Topic: ORA-30372: fine grain access policy conflicts with materialized view
Next Topic: Get the MetaData for FGA Policy
Goto Forum:
  


Current Time: Sun May 19 13:12:41 CDT 2024