Home » SQL & PL/SQL » SQL & PL/SQL » To extract the previous month from current month? (Oracle 10g)
To extract the previous month from current month? [message #355129] Wed, 22 October 2008 15:14 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,
I need a kind suggestion on this scenario.I have a procedure which I execute manually.
For example EXEC PROC_TEST('01-OCT-2008') which will give me the results of this month data.But the date format which I am using is as the above example.Trying slightly with different technical angle for below example.
EXEC PROC_TEST('01-EXTRACT(MONTH FROM (ADD_MONTHS(SYSDATE,-2)))-2008').Which throws error invalid sql statement.Could anyone help me on this part.



Thanks and Regards,
Hammer

[Updated on: Wed, 22 October 2008 15:18]

Report message to a moderator

Re: To extract the previous month from current month? [message #355130 is a reply to message #355129] Wed, 22 October 2008 15:30 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aviva4500,

Use concatenation operator (||) before and after your Extract Function. And use TO_CHAR Function instead of your Extract Function.


***Added (another way)

Since you want 1st of the required month then you can avoid the concat operators and use To_CHAR and TRUNC Function alone. I mean something like this will also work:

SQL> SELECT TO_CHAR (TRUNC (ADD_MONTHS (SYSDATE, -2), 'MONTH'), 'DD-Mon-YYYY')
  2    FROM DUAL;

TO_CHAR(TRU
-----------
01-Aug-2008


Regards,
Jo

[Updated on: Wed, 22 October 2008 15:38]

Report message to a moderator

Re: To extract the previous month from current month? [message #355131 is a reply to message #355130] Wed, 22 October 2008 15:39 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Joicejohn,
Thanks for your immediate response.I am very happy for that.This is the one which i need to do .
EXEC PROC_TEST('01-||EXTRACT(MONTH FROM (ADD_MONTHS(TO_CHAR(SYSDATE,-2))))||-2008').If not i would like you to correct me where I am missing.



Thanks and Regards.
Hammer.
Re: To extract the previous month from current month? [message #355133 is a reply to message #355131] Wed, 22 October 2008 15:43 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@aviva4500,

Extract Function For Month will return the month number not the month name or any abbreviated form of the month. Please use TO_CHAR Function as I have demonstrated in my previous post.

(I had updated my previous reply)

Regards,
Jo
Re: To extract the previous month from current month? [message #355134 is a reply to message #355133] Wed, 22 October 2008 15:47 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Joicejohn,
Thankyou very much.You have been very helpfull.



Thanks and Regards,
Hammer.
Re: To extract the previous month from current month? [message #355483 is a reply to message #355134] Fri, 24 October 2008 07:14 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
@aviva4500,

I thought this will be helpful for you


SQL> select to_date('01-'||extract( month from ( add_months(sysdate,-2)))||'-2008','dd-mm-yy') from 
dual;

TO_DATE('
---------
01-AUG-08
Re: To extract the previous month from current month? [message #355505 is a reply to message #355483] Fri, 24 October 2008 09:23 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@panyam,

Funny I just mentioned almost similar problem in another thread.

Please see the below codes and notice the difference in the output:
SQL>  alter session set nls_date_format= 'DD-MON-YYYY';

Session altered.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
-----------
24-OCT-2008

SQL> SELECT To_date('01-'
  2                 ||EXTRACT(MONTH FROM (Add_months(SYSDATE,- 2)))
  3                 ||'-2008','dd-mm-yy')
  4  FROM   Dual;

TO_DATE('01
-----------
01-AUG-2008

SQL> alter session set nls_date_format= 'MM-DD-YYYY';

Session altered.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
----------
10-24-2008

SQL> SELECT To_date('01-'
  2                 ||EXTRACT(MONTH FROM (Add_months(SYSDATE,- 2)))
  3                 ||'-2008','dd-mm-yy')
  4  FROM   Dual;

TO_DATE('0
----------
08-01-2008


***Added:
Morever, you used a format_mask 'dd-mm-yy', so you should have been expecting '08-01-08' but still you got '01-Aug-2008'. Didn't it ring a bell???

Regards,
Jo

[Updated on: Fri, 24 October 2008 09:30]

Report message to a moderator

Re: To extract the previous month from current month? [message #355507 is a reply to message #355505] Fri, 24 October 2008 09:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
So? The outcome of the query is a date. The representation of that date depends on your nls-settings.
The date however is the same for both examples, so I don't really see your point.
Re: To extract the previous month from current month? [message #355659 is a reply to message #355507] Sun, 26 October 2008 23:02 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Hi Frank,

I didn't say the output given by @panyam is wrong. In fact it will work perfectly if you are passing a date value to the procedure. But I don't think OP is passing a date. He is passing a string which follows a particular date format.

Not only does the representation of date depends on the nls-settings but so does Oracle's implicit conversion from Date to String and vice-versa.

Hope the following code helps you to understand what I am pointing at:

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 27 09:15:11 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: test_schema
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set serveroutput on;
SQL> CREATE OR REPLACE PROCEDURE Date_Test
  2       (t_Date  IN VARCHAR2)
  3  AS
  4  BEGIN
  5    dbms_Output.Put_Line('Value of t_date: '
  6                         ||t_Date);
  7  END;
  8  /

Procedure created.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
---------
27-OCT-08

SQL> EXEC Date_Test(sysdate);
Value of t_date: 27-OCT-08

PL/SQL procedure successfully completed.

SQL> alter session set nls_date_format='MM-DD-YYYY';

Session altered.

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
----------
10-27-2008

SQL> EXEC Date_Test(sysdate);
Value of t_date: 10-27-2008

PL/SQL procedure successfully completed.



Regards,
Jo
Previous Topic: Regarding DEGREE column in ALL_TABLES
Next Topic: Duplicate Report
Goto Forum:
  


Current Time: Sun Dec 11 06:19:25 CST 2016

Total time taken to generate the page: 0.29466 seconds