Re: apply rtrim on DATE data

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 5 Nov 2007 21:23:01 +0100
Message-ID: <472f7bc0$0$230$e4fe514c@news.xs4all.nl>

"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1194283475.697059_at_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

IT'S STATED IN THE ORIGINAL POST!!!!! I JUST QUOTED!!!!!! >>>>>DTE_SYSDATE is a DATE type field in the table A_T_PR_TAXONOMY.

Sorry for shouting <g>

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

Shakespeare Received on Mon Nov 05 2007 - 14:23:01 CST