Home » SQL & PL/SQL » SQL & PL/SQL » How to pass DATE parameter to View
How to pass DATE parameter to View [message #302227] Mon, 25 February 2008 00:14 Go to next message
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 #302239 is a reply to message #302227] Mon, 25 February 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where mydatecol = to_date('mydateparam','mydateformat')

Is this what you want?
Otherwise, post more.
For instance, what kind of report do you have? VBA program/script, SQL script, other?

Regards
Michel

[Updated on: Mon, 25 February 2008 00:46]

Report message to a moderator

Re: How to pass DATE parameter to View [message #302338 is a reply to message #302239] Mon, 25 February 2008 05:02 Go to previous messageGo to next message
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 #302342 is a reply to message #302338] Mon, 25 February 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use the way I posted with possibly between.
If you are using Excel and ODBC, the values may be in cells.

Regards
Michel
Re: How to pass DATE parameter to View [message #302353 is a reply to message #302342] Mon, 25 February 2008 05:34 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi Michel,

so i have to define

mydateparam and mydateforamt in the SQL.
please advice.

where mydatecol = to_date('mydateparam','mydateformat')

Rgds
sydney
Re: How to pass DATE parameter to View [message #302360 is a reply to message #302353] Mon, 25 February 2008 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"mydateparam" is your parameter, your cell number or whatever.
"mydateformat" is this cell format (which must be a string), see Datetime Format Models

Regards
Michel
Re: How to pass DATE parameter to View [message #302396 is a reply to message #302338] Mon, 25 February 2008 08:09 Go to previous messageGo to next message
joy_division
Messages: 4644
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:
19-9-2010
1CRAZYDUDE


Re: How to pass DATE parameter to View [message #302723 is a reply to message #302396] Tue, 26 February 2008 17:31 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #302729 is a reply to message #302227] Tue, 26 February 2008 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
so what does your date string look like?
Re: How to pass DATE parameter to View [message #302746 is a reply to message #302729] Tue, 26 February 2008 21:19 Go to previous messageGo to next message
sydney1
Messages: 65
Registered: November 2006
Location: sydney
Member
Hi anacedent thanks for your help
I am sorry can't get what you mean by ' how date string looks like'
plese explain i will send you the output of it then

Thanks again for your help

Sydney
Re: How to pass DATE parameter to View [message #302747 is a reply to message #302227] Tue, 26 February 2008 21:49 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>last_day(to_date('&Date','DD/MM/YYYY'))
something gets substituted for "&Date", so post what is replaced.
Re: How to pass DATE parameter to View [message #302756 is a reply to message #302747] Tue, 26 February 2008 22:40 Go to previous messageGo to next message
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


Re: How to pass DATE parameter to View [message #302771 is a reply to message #302756] Wed, 27 February 2008 00:20 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Trunc" both current date and created date, if they are equal then you are in the same month.
Or you can check if your date is between the "trunc" of current date and the "last_day" of current date.

Regards
Michel

[Updated on: Wed, 27 February 2008 00:21]

Report message to a moderator

Previous Topic: Adding ^M to each line in output file
Next Topic: SQL query for finding out the depentent tables
Goto Forum:
  


Current Time: Fri Dec 09 15:44:15 CST 2016

Total time taken to generate the page: 0.32856 seconds