How to pass date to the query [message #383023] |
Tue, 27 January 2009 00:18  |
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 #383040 is a reply to message #383023] |
Tue, 27 January 2009 00:45   |
Frank
Messages: 7901 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   |
 |
Littlefoot
Messages: 21823 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
|
|
|
|
|
|