Home » SQL & PL/SQL » SQL & PL/SQL » How to pass date to the query
How to pass date to the query [message #383023] Tue, 27 January 2009 00:18 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi ,
My query is like this I have to pass date filter but it isnot showing me proper o/p
SELECT fnccgvalue(fromcurrencyid, 'D', 10) AS "from_currency",
       exchangerate,
       fnccgvalue(tocurrencyid, 'D', 10) AS "to_currency",
       effectivedate
from  ulsdemo.exchange_rate_master_hst
 where trunc(to_char(effectivedate,'dd/mm/yyyy')) like trunc(to_char('01/12/2009','dd/mm/yyyy'))
 


please tell me any suggestion

[EDITED by LF: applied [code] tags]

[Updated on: Tue, 27 January 2009 00:45] by Moderator

Report message to a moderator

Re: How to pass date to the query [message #383025 is a reply to message #383023] Tue, 27 January 2009 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: How to pass date to the query [message #383026 is a reply to message #383023] Tue, 27 January 2009 00:23 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use = instead of LIKE

And what is the proper output you are looking for.

[Updated on: Tue, 27 January 2009 00:26]

Report message to a moderator

Re: How to pass date to the query [message #383027 is a reply to message #383023] Tue, 27 January 2009 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>(to_char('01/12/2009','dd/mm/yyyy'))
HUH?
TO_CHAR is designed to convert a DATE to a string; but this operates against '01/12/2009' which is already a string.
Re: How to pass date to the query [message #383036 is a reply to message #383023] Tue, 27 January 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
" trunc(to_char(effectivedate,'dd/mm/yyyy')) "
What does this mean? How do you truncate a string? Oracle does not know this, what do you think it will do?

Regards
Michel
Re: How to pass date to the query [message #383040 is a reply to message #383023] Tue, 27 January 2009 00:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sr_orcl wrote on Tue, 27 January 2009 07:18[code

where trunc(to_char(effectivedate,'dd/mm/yyyy')) like trunc(to_char('01/12/2009','dd/mm/yyyy'))[/code]

As BlackSwan said: to_char is used on dates (or numbers)
Trunc also is NOT a function performed on strings.

Learn how to compare dates, don't convert them to strings if you don't need to.
If you have an index on effectivedate, you don't want to use any functions on it (unless it is an FBI)

options:
where  effectivedate between to_date('01-12-2009 00:00:00', 'dd-mm-yyyy hh24:mi:ss') and to_date('01-12-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss')


alternative (if you have no index on effectivedate):
where  trunc(effectivedate) = to_date('01-12-2009', 'dd-mm-yyyy')

[Updated on: Tue, 27 January 2009 00:46]

Report message to a moderator

Re: How to pass date to the query [message #383043 is a reply to message #383023] Tue, 27 January 2009 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 20893
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
sr_orcl
where trunc(to_char(effectivedate,'dd/mm/yyyy')) like trunc(to_char('01/12/2009','dd/mm/yyyy'))


When dealing with dates, try to write a query that really uses dates, not strings.

As it appears that you'd like to select all records where "effectivedate" column's value equals 1st of December 2009; truncating this date datatype column makes sense, because - if it contains time component, you want to remove it.

In order to keep it in the DATE domain, you'd probably want to put it as follows:
where trunc(effectivedate) = to_date('01.12.2009', 'dd.mm.yyyy')
        ^                              ^
        |                              |
 date without time          string ('01.12.2009') converted to date
                            with help of the TO_DATE function

Now that you have dates on both sides of the "=" sign, this *might* do the job.


Oh, sorry, I was writing an answer and doing something else at the same time and didn't see Frank's answer.

[Updated on: Tue, 27 January 2009 01:00]

Report message to a moderator

Re: How to pass date to the query [message #383143 is a reply to message #383040] Tue, 27 January 2009 06:58 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
Quote:
(unless it is an FBI)
what does it mean by FBI
yours
dr.s.raghunathan
Re: How to pass date to the query [message #383148 is a reply to message #383023] Tue, 27 January 2009 07:15 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Function Based Index
Re: How to pass date to the query [message #383149 is a reply to message #383148] Tue, 27 January 2009 07:18 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
thank you very much
Previous Topic: which is faster
Next Topic: URGENT:Deciding function or procedure at runtime.. (merged)
Goto Forum:
  


Current Time: Mon Dec 05 05:04:51 CST 2016

Total time taken to generate the page: 0.10098 seconds