Re: Date rounding problem

From: ddf <oratune_at_msn.com>
Date: Fri, 5 Apr 2013 13:15:42 -0700 (PDT)
Message-ID: <255ceb35-baf5-4863-b2ca-c52edf7fecfa_at_googlegroups.com>

On Friday, April 5, 2013 1:17:53 PM UTC-6, Syltrem wrote:
> 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>
Is this what you're seeing:

SQL> select *
2 from v$nls_parameters
3 where parameter = 'NLS_DATE_FORMAT';

PARAMETER

VALUE

NLS_DATE_FORMAT
DD-MON-RR SQL>
SQL> select to_char(round(to_date(sysdate)),'DD-MON-YYYY HH24:MI') from dual;