Menu
I got a bug report where Oracle 10g was truncating return values from to_char(datetime):
SQL> select to_char(systimestamp, '"day:"DD"hello"') from dual;
TO_CHAR(SYSTIMESTAMP,'"DAY:"DD"HE
---------------------------------
day:27hel
Notably, this does not appear to happen in Oracle 11g. My question is, why does it happen at all? Is there some configuration variable to set to tell to_char(datetime) to allocate a bigger buffer for its return value?
539
To_char formats a DATE into a string using the given format mask. To_date converts a STRING into a date using the format mask.
TO_CHAR (datetime) converts a datetime or interval value of DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt .
Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY.
SQL Server has separate functions for YEAR() , MONTH() , and DAY() . Oracle uses TO_CHAR() ; SQL Server uses CONVERT() . One option is to define the functions YEAR() , MONTH() , and DAY() in Oracle and then use string concatenation (via the CONCAT() ) function to combine the data.
I'm not sure but it might be just displaying in SQL*Plus. Have you tried to run it in Toad? Or if you assign result to varchar2 in PL/SQL block and output result?
Here what I've found in SQL*Plus Reference for 10g:
The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9. See the FORMAT clause of the COLUMN command for more information on formatting DATE columns.
Your values is trimmed to 9 characters which corresponds to default A9 format. I don't have same version and this behaviour is not reproducing in 11g so can you please check my theory?
157
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
