Home » SQL & PL/SQL » SQL & PL/SQL » Using DATES
Using DATES [message #266231] Mon, 10 September 2007 04:40 Go to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

SQL> DESC DAT
Name Null? Type
----------------------------------- -------- --------
A DATE
B NUMBER(9,3)

SQL> SELECT *FROM DAT;

A B
--------- ----------
10-JAN-07 5000
15-FEB-07 8000

When I am giving START DATE 12-JAN-2007 (or) < 15-FEB-2007 Then
I want to Fetch amount from DAT(B) 10-JAN-2007 ---> 5000

When I am giving START DATE 17-FEB-2007 (or) > 15-FEB-2007 Then
I want to Fetch amount from DAT(B) 15-FEB-07 ---> 8000

Sorry Michel Please Help me

[Updated on: Mon, 10 September 2007 04:56]

Report message to a moderator

Re: Using DATES [message #266235 is a reply to message #266231] Mon, 10 September 2007 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When I am giving START DATE 12-JAN-2007
I want to Fetch amount from DAT(B) 10-JAN-2007

When I am giving START DATE 17-FEB-2007
I want to Fetch amount from DAT(B) 15-FEB-07

Why? Because it is 2 days before?

In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Using DATES [message #266243 is a reply to message #266235] Mon, 10 September 2007 04:58 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

I have Clearly updated help me michel
Re: Using DATES [message #266254 is a reply to message #266231] Mon, 10 September 2007 05:19 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
select A, B from
(
select rank() over (order by A desc) rn, A , B from DAT where  A < '1-may-2007'
)
where rn = 1 
Re: Using DATES [message #266259 is a reply to message #266254] Mon, 10 September 2007 05:55 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
where  A < '1-may-2007'

Why would you compare dates to strings?
Re: Using DATES [message #266265 is a reply to message #266243] Mon, 10 September 2007 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have Clearly updated help me michel

1/ You don't format your post
2/ You don't say in WORDS what you want.
If I understand, you want the latest row (order by A column) before or equal to the date you give. Is this that? Or am I misinterpreting your example? But why do I have to interpret? Don't you have to clearly state what you want, don't you?

Regards
Michel

Re: Using DATES [message #266309 is a reply to message #266265] Mon, 10 September 2007 08:24 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Thanks for sharing your gold time Michel. I have implemented this part in my project.

CREATE OR REPLACE PROCEDURE PRO(P_DT DATE)
IS
V_SD DATE;
V_AMT NUMBER(10,3);
BEGIN
SELECT MAX(A) INTO V_SD
FROM DAT
WHERE A < P_DT;
SELECT B INTO V_AMT
FROM DAT
WHERE A = V_SD;
DBMS_OUTPUT.PUT_LINE(V_SD);
END PRO;
/

SQL> DESC DAT
Name Null? Type
----------------------------------------------------------------------------- -------- --------------
A DATE
B NUMBER(9,3)

SQL> SELECT *FROM DAT;

A B
--------- ----------
10-JAN-07 5000
15-FEB-07 8000
20-MAY-07 12000

SQL> EXEC PRO('25-JAN-2007');
5000

PL/SQL procedure successfully completed.

SQL> EXEC PRO('30-JAN-2007');
5000

PL/SQL procedure successfully completed.


SQL> EXEC PRO('19-MAY-2007');
8000

PL/SQL procedure successfully completed.

SQL> EXEC PRO('25-JULY-2007');
12000

PL/SQL procedure successfully completed.
Re: Using DATES [message #266314 is a reply to message #266309] Mon, 10 September 2007 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Thanks for sharing your gold time Michel.

Thanks for not wasting my gold time:
Quote:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.


PL/SQL is useless, use SQL.

Regards
Michel

Re: Using DATES [message #266380 is a reply to message #266309] Mon, 10 September 2007 12:24 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
thani_oracle wrote on Mon, 10 September 2007 09:24

SQL> EXEC PRO('25-JULY-2007');



Still invalid, as you are passing in a STRING to a functions requiring a DATE.
FOO SCOTT>alter session set nls_date_format='mm/dd/yyyy hh24:mi:ss';

Session altered.

FOO SCOTT>EXEC PRO('25-JAN-2007');
BEGIN PRO('25-JAN-2007'); END;

*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1



Re: Using DATES [message #266471 is a reply to message #266231] Tue, 11 September 2007 00:05 Go to previous messageGo to next message
mylapuram
Messages: 8
Registered: April 2007
Location: Bangalore
Junior Member
what ever u did in pl/sql using procedure same i did using subquery in sql.

select B from dat where
a = (select max(a) from dat where a<='17-feb-2007')


Thanks
mylapuram
Re: Using DATES [message #266476 is a reply to message #266231] Tue, 11 September 2007 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>what ever u did in pl/sql using procedure same i did using subquery in sql.
and blew up with errors when NLS_DATE_FORMAT changes.

With Oracle characters between single quote marks are STRINGS; such as: 'The following, 2007-feb-29, is a STRING; not a date'.
Re: Using DATES [message #266478 is a reply to message #266231] Tue, 11 September 2007 00:16 Go to previous messageGo to next message
mylapuram
Messages: 8
Registered: April 2007
Location: Bangalore
Junior Member
what ever u did in pl/sql using procedure same i did using subquery in sql.
and blew up with errors when NLS_DATE_FORMAT changes.

With Oracle characters between single quote marks are STRINGS; such as: 'The following, 2007-feb-29, is a STRING; not a date'.


then use to_date

select B from dat where
a = (select max(a) from dat where a<=to_date('17-feb-2007','dd-mon-yyyy'))
Re: Using DATES [message #266532 is a reply to message #266314] Tue, 11 September 2007 01:53 Go to previous message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

ok michel
Previous Topic: Explain plan output.
Next Topic: Warning: Trigger altered with compilation errors.
Goto Forum:
  


Current Time: Fri Dec 02 23:12:02 CST 2016

Total time taken to generate the page: 0.08935 seconds