Re: Date rounding problem
Date: Fri, 05 Apr 2013 15:17:53 -0400
Message-ID: <ii8ul8tnjr5ejjf3u96rkbifhgrf4o9pln_at_4ax.com>
Oops - I forgot to mention something important
The problem only appears when I do to_date(sysdate) which I know is stupid, but it comes from a procedure I was trying to run when I found out it wouldn't run in certain databases.
I since removed the unnecessary to_date() in the code but still, nls_date_format has something to do with the problem I saw. Maybe just a bug ?
--> This is fine
SQL> select to_char(round(sysdate,'HH24'),'DD-MON-YYYY HH24:MI') from
dual;
TO_CHAR(ROUND(SYS
05-APR-2013 15:00 1 ligne selectionnee.
--> This is NOT
SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY
HH24:MI') from dual;
TO_CHAR(ROUND(TO_
05-APR-2013 00:00 1 ligne selectionnee.
Thanks
Syltrem
On Fri, 05 Apr 2013 15:07:37 -0400, Syltrem <syltremzulu_at_videotron.ca> wrote:
>Hi
>
>There's something I don't understand here with dates vs
>NLS_DATE_FORMAT
>
>I used ROUND() around SYSDATE, and in different databases I was
>getting different values...
>
>I found out the problem was with the setting of the paremeter. When
>NOT set, it would round to the day, not hours as I am asking.
>
>
>Can someone care to explain ? I aml truly lost here and can't see the
>logic in that.
>
>Thanks
>
>
>
>
>
>SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 5 14:52:20 2013
>
>Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
>
>
>Connecte a :
>Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
>With the Partitioning, Data Mining and Real Application Testing
>options
>
>
>SQL> show parameter nls_date_format
>
>NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
>-------------------------------- -----------
>-----------------------------------
>nls_date_format string
>SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY
>HH24:MI') from dual;
>
>TO_CHAR(ROUND(TO_
>-----------------
>05-APR-2013 00:00
>
>1 ligne selectionnee.
>
>SQL> alter session set nls_date_format ='DD-MON-YYYY HH24:MI:SS';
>
>Session modifiee.
>
>SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY
>HH24:MI') from dual;
>
>TO_CHAR(ROUND(TO_
>-----------------
>05-APR-2013 15:00
>
>1 ligne selectionnee.
>
>SQL>
Received on Fri Apr 05 2013 - 21:17:53 CEST