Date rounding problem

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Fri, 05 Apr 2013 15:07:37 -0400
Message-ID: <ho7ul8tledn55o0nqsd551pvfvca9u90ic_at_4ax.com>

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:07:37 CEST