Hi All,
The sql below does not return anything,
select DTE_SYSDATE
from A_T_PR_TAXONOMY
WHERE DTE_SYSDATE = to_date( 20071031, 'yyyymmdd')

while these two return some rows
select DTE_SYSDATE
from A_T_PR_TAXONOMY
WHERE rtrim(DTE_SYSDATE) = to_date( 20071031, 'yyyymmdd')

select DTE_SYSDATE
from A_T_PR_TAXONOMY
WHERE DTE_SYSDATE LIKE to_date( 20071031, 'yyyymmdd')


results:
31-OCT-07
31-OCT-07
31-OCT-07
31-OCT-07

DTE_SYSDATE is a DATE type field in the table A_T_PR_TAXONOMY. I am
confused at why the rtrim can make this difference.

ouyang.jie (AT) gmail (DOT) com wrote:
Things work better when done according to the docs:

1. Pass TO_DATE strings not integers: '20071031'

2. DTE_SYSDATE does not exist except perhaps in your database and we
have no idea what it is.

3. Oracle dates ... ALL Oracle dates ... include the time.

Do the following in SQL*Plus:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
whenever working with dates so you can see what is actually going on.

Best practice is to use TRUNC. Look at the last TRUNC demos here near
page bottom. http://www.psoug.org/reference/date_func.html
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Nov 1, 3:50 pm, ouyang.... (AT) gmail (DOT) com wrote:
The Oracle date datatype contains both a date and a time component.

Use trunc(date_column) = trunc(sysdate)

or better yet a range test
where datecol .= to_date('some date') -- defaults to midnight
and datecol < to_date('1 day greater than what you want','format')

See the SQL manual for a table of date format meanings.
see to_date and to_char

HTH -- Mark D Powell --

"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht
news:1193949870.690784 (AT) bubbleator (DOT) drizzle.com...
DA:

Ad 2): Stop drinking and start reading:
***** DTE_SYSDATE is a DATE type field in the table A_T_PR_TAXONOMY. I am
***** confused at why the rtrim can make this difference.


Shakespeare

Shakespeare wrote:
Long on attitude short on facts there Shakespeare. How should I know
DTE_SYSDATE is a date field? The fact that someone used TO_DATE in
their post? That's a big leap in my book.

Try try this:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT DISTINCT dte_sysdate
FROM a_t_pr_taxonomy
WHERE TO_CHAR(dte_sysdate) LIKE '%20071031%'
ORDER BY 1;

SELECT TO_DATE(20071031, 'yyyymmdd') FROM dual;

Do you see any matches?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht
news:1194283475.697059 (AT) bubbleator (DOT) drizzle.com...

IT'S STATED IN THE ORIGINAL POST!!!!! I JUST QUOTED!!!!!!
Sorry for shouting <g>

And yes it's a column, not a field..... ;-)

Shakespeare