Re: apply rtrim on DATE data

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 05 Nov 2007 09:24:39 -0800
Message-ID: <1194283475.697059@bubbleator.drizzle.com>

Shakespeare wrote:
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> news:1193949870.690784_at_bubbleator.drizzle.com...

>> ouyang.jie_at_gmail.com wrote:
>>> Hi All,
>>> The sql below does not return anything,
>>> select  DTE_SYSDATE
>>>         from A_T_PR_TAXONOMY
>>> WHERE DTE_SYSDATE = to_date( 20071031, 'yyyymmdd')
>>>
>>> snip
>>>
>>> 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.
>> 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.

>
> 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

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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Nov 05 2007 - 11:24:39 CST