Re: trunc(sysdate) with dd-mon-rr format

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 20 Jun 1998 02:01:25 GMT
Message-ID: <358e16bd.4891874@192.86.155.100>

A copy of this was sent to "Steve Pedersen" <steve_at_here.com> (if that email address didn't require changing) On 19 Jun 1998 21:28:42 GMT, you wrote:

>I have come accross an interesting problem and wondered if anyone else has
>experienced it. If the nls_date_format is set to dd-mon-rr in the init.ora
>file, then if you "select trunc(sysdate,'DD-MON-RR') from dual", it returns
>the error message ora -1898 too many precision specifiers. This is also the
>case with YYYY and YY as the year mask. As many programs use trunc
>(date_column_of_some_type), this is potentially a problem.
>
>While I would prefer to use the nls_date_format of DD-MON-YYYY, in this
>case I cannot. If anybody has had experience with this or could offer some
>insight, please email me at steve_pedersen_at_northwood.ca
>
>Thanks.

the problem is an ambigous error message, the message should read something more like:

dd-mon-rr is a format mask for displaying or converting input data and can be used with to_char/to_date. It is not a format that is valid to be used with trunc. The inputs for trunc on a date field are listed in chapter 3 of the sql language reference manual...

So, dd-mon-rr just isn't valid input for trunc. If you want to trunc to the

hour:    trunc( sysdate, 'hh' )
day:     trunc( sysdate, 'ddd' )
week:    trunc( sysdate, 'day' )
month:   trunc( sysdate, 'mm' )
quarter: trunc(sysdate, 'q' )
year:    trunc( sysdate, 'iy' );

among others..... there is a complete table of valid formats in the doc.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jun 19 1998 - 21:01:25 CDT