Re: apply rtrim on DATE data

On Nov 1, 3:50 pm, ouyang...._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.

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 -- Received on Thu Nov 01 2007 - 15:48:25 CDT