X-Received: by 10.236.149.147 with SMTP id x19mr5553807yhj.31.1365192944631; Fri, 05 Apr 2013 13:15:44 -0700 (PDT) X-Received: by 10.50.187.133 with SMTP id fs5mr110041igc.12.1365192944167; Fri, 05 Apr 2013 13:15:44 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feeder1.cambriumusenet.nl!feed.tweaknews.nl!209.85.216.87.MISMATCH!t2no40359113qal.0!news-out.google.com!ef9ni7624qab.0!nntp.google.com!ca1no30814155qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Fri, 5 Apr 2013 13:15:42 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <255ceb35-baf5-4863-b2ca-c52edf7fecfa@googlegroups.com> Subject: Re: Date rounding problem From: ddf Injection-Date: Fri, 05 Apr 2013 20:15:44 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl On Friday, April 5, 2013 1:17:53 PM UTC-6, Syltrem wrote: > Oops - I forgot to mention something important >=20 >=20 >=20 > The problem only appears when I do to_date(sysdate) which I know is >=20 > stupid, but it comes from a procedure I was trying to run when I found >=20 > out it wouldn't run in certain databases. >=20 >=20 >=20 > I since removed the unnecessary to_date() in the code but still, >=20 > nls_date_format has something to do with the problem I saw. >=20 > Maybe just a bug ? >=20 >=20 >=20 >=20 >=20 > --> This is fine >=20 > SQL> select to_char(round(sysdate,'HH24'),'DD-MON-YYYY HH24:MI') from >=20 > dual; >=20 >=20 >=20 > TO_CHAR(ROUND(SYS >=20 > ----------------- >=20 > 05-APR-2013 15:00 >=20 >=20 >=20 > 1 ligne selectionnee. >=20 >=20 >=20 > --> This is NOT >=20 > SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY >=20 > HH24:MI') from dual; >=20 >=20 >=20 > TO_CHAR(ROUND(TO_ >=20 > ----------------- >=20 > 05-APR-2013 00:00 >=20 >=20 >=20 > 1 ligne selectionnee. >=20 >=20 >=20 >=20 >=20 > Thanks >=20 > Syltrem >=20 >=20 >=20 >=20 >=20 > On Fri, 05 Apr 2013 15:07:37 -0400, Syltrem >=20 > wrote: >=20 >=20 >=20 > >Hi >=20 > > >=20 > >There's something I don't understand here with dates vs >=20 > >NLS_DATE_FORMAT >=20 > > >=20 > >I used ROUND() around SYSDATE, and in different databases I was >=20 > >getting different values... >=20 > > >=20 > >I found out the problem was with the setting of the paremeter. When >=20 > >NOT set, it would round to the day, not hours as I am asking. >=20 > > >=20 > > >=20 > >Can someone care to explain ? I aml truly lost here and can't see the >=20 > >logic in that. >=20 > > >=20 > >Thanks >=20 > > >=20 > > >=20 > > >=20 > > >=20 > > >=20 > >SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 5 14:52:20 2013 >=20 > > >=20 > >Copyright (c) 1982, 2007, Oracle. All Rights Reserved. >=20 > > >=20 > > >=20 > >Connecte a : >=20 > >Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production >=20 > >With the Partitioning, Data Mining and Real Application Testing >=20 > >options >=20 > > >=20 > > >=20 > >SQL> show parameter nls_date_format >=20 > > >=20 > >NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM >=20 > >-------------------------------- ----------- >=20 > >----------------------------------- >=20 > >nls_date_format string >=20 > >SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY >=20 > >HH24:MI') from dual; >=20 > > >=20 > >TO_CHAR(ROUND(TO_ >=20 > >----------------- >=20 > >05-APR-2013 00:00 >=20 > > >=20 > >1 ligne selectionnee. >=20 > > >=20 > >SQL> alter session set nls_date_format =3D'DD-MON-YYYY HH24:MI:SS'; >=20 > > >=20 > >Session modifiee. >=20 > > >=20 > >SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY >=20 > >HH24:MI') from dual; >=20 > > >=20 > >TO_CHAR(ROUND(TO_ >=20 > >----------------- >=20 > >05-APR-2013 15:00 >=20 > > >=20 > >1 ligne selectionnee. >=20 > > >=20 > >SQL> Is this what you're seeing: SQL> select * 2 from v$nls_parameters 3 where parameter =3D '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 dua= l; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 05-APR-2013 00:00 SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY HH24:MI') f= rom dual; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 05-APR-2013 00:00 SQL> SQL> alter session set nls_date_Format =3D 'DD-MM-RRRR HH24'; Session altered. SQL> select to_char(round(to_date(sysdate)),'DD-MON-YYYY HH24:MI') from dua= l; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 06-APR-2013 00:00 SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY HH24:MI') f= rom dual; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 05-APR-2013 13:00 SQL> SQL> alter session set nls_date_Format =3D 'DD-MM-RRRR HH24:MI'; Session altered. SQL> select to_char(round(to_date(sysdate)),'DD-MON-YYYY HH24:MI') from dua= l; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 06-APR-2013 00:00 SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY HH24:MI') f= rom dual; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 05-APR-2013 14:00 SQL> SQL> alter session set nls_date_Format =3D 'DD-MM-RRRR HH24:MI:SS'; Session altered. SQL> select to_char(round(to_date(sysdate)),'DD-MON-YYYY HH24:MI') from dua= l; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 06-APR-2013 00:00 SQL> select to_char(round(to_date(sysdate),'HH24'),'DD-MON-YYYY HH24:MI') f= rom dual; TO_CHAR(ROUND(TO_DATE(SYSD -------------------------- 05-APR-2013 14:00 SQL> If NLS_DATE_FORMAT is set to return any part of the time portion of a date = you will get the time rounded, but as you see if you don't include at least= the minutes it rounds the hour (notice that format returns 13:00 when the = other formats which include minutes return 14:00). David Fitzjarrell