How to pass DATE parameter to View [message #302227] |
Mon, 25 February 2008 00:14  |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Gurus,
I have written a view to get the report from oracle 9 database.
But there is too much data in the table. I want to pass the date parameter my SQL script. Could you guys please help.
The report is going to be accessed by manager via excel.
thanks in advance
sydney
|
|
|
|
Re: How to pass DATE parameter to View [message #302338 is a reply to message #302239] |
Mon, 25 February 2008 05:02   |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Thanks Michel for your quick reply.
what i am doing is like in SQL in oracle.
select * from testtable where date between '10-2-2008' to '20-2-2008';
This is not the query i am running i am not at work so can't send you exact query.
our managers want to get data from this table using dates they want.
as there is lot of data and i want to pass the DATE parameter if possible.
They want to connect to database using ODBC via Excel and get the data they want.
If this explains.
Thanks again for your help.
Rgds
sydney
|
|
|
|
|
|
Re: How to pass DATE parameter to View [message #302396 is a reply to message #302338] |
Mon, 25 February 2008 08:09   |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
sydney1 wrote on Mon, 25 February 2008 06:02 |
what i am doing is like in SQL in oracle.
select * from testtable where date between '10-2-2008' to '20-2-2008';
|
This is not the proper use of DATEs in Oracle. This is comparing a DATE column to a String. Values that fall between your string literals are:
|
|
|
Re: How to pass DATE parameter to View [message #302723 is a reply to message #302396] |
Tue, 26 February 2008 17:31   |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Joy/Michel,
Thanks for your replies.
My problem now is i want to get the data only for current month.
example if user connect to database via ODBC and they will get only data for current month. if they logon in FEB month they should get only data for FEb and same like in march.
I am trying something like
trunc(sysdate,'mm')
but can't get all the data for current month.
i am doing like below:
select * from test
where createddate IN trunc(sysdate,'mm')
I can get the perivious month like
select * from test
where createddate BETWEEN ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -0)
AND TRUNC (SYSDATE, 'MM') - 0
[/pre]
Please advice.
Rgds
sydney
|
|
|
Re: How to pass DATE parameter to View [message #302726 is a reply to message #302396] |
Tue, 26 February 2008 18:14   |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi joy,
i am successfull passing the date parameters. but when i connect from ODBC it is throwing an error that
[oracle][odbc][ora]ORA-01858: a non-numeric character was found where a numeric was expected
i am using as below in sql
select * from test
where createddate >= to_date('&Date','DD/MM/YYYY')
and
createddate<= last_day(to_date('&Date','DD/MM/YYYY'))
)
please suggest if we can convert dates to non-numeric so i can select the dates from Excel ODBC.
Rgds
Sydney
|
|
|
|
|
|
Re: How to pass DATE parameter to View [message #302756 is a reply to message #302747] |
Tue, 26 February 2008 22:40   |
sydney1
Messages: 65 Registered: November 2006 Location: sydney
|
Member |
|
|
Hi Anacedent,
i have writtent he quey like
select * from test
where createddate >= to_date('&Date','DD/MM/YYYY')
and
createddate<= last_day(to_date('&Date','DD/MM/YYYY'))
But when i want to get the result in EXCEL using ODBC then it is giving an error.
My wuestion is Can we pass the dates with some other way in EXCEl or use different way in SQL query.
CAN YOU PLEASE GUIDE ME HOW TO GET CURRENT MONTH IN QUERY AS WELL
I AM ABLE TO GET PERIVIOUS MONTH BUT CAN'T GET CURRENT MONTH.
PLEASE HELP
select * from test
where createddate BETWEEN ADD_MONTHS (TRUNC (SYSDATE, 'MM'), -0)
AND TRUNC (SYSDATE, 'MM') - 0
Rgds
Sydney
|
|
|
|