| To extract the previous month from current month? [message #355129] |
Wed, 22 October 2008 15:14  |
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  |
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 |
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 #355505 is a reply to message #355483] |
Fri, 24 October 2008 09:23 |
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 #355659 is a reply to message #355507] |
Sun, 26 October 2008 23:02 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
