Re: apply rtrim on DATE data

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')
>
> 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.

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