Date rounding problem

[Syltrem]

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>

[Syltrem]

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 <syltr...@videotron.ca>
wrote:

[ddf]

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;

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') from dual;

TO_CHAR(ROUND(TO_DATE(SYSD
--------------------------
05-APR-2013 00:00

SQL>
SQL> alter session set nls_date_Format = 'DD-MM-RRRR HH24';

Session altered.

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

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') from dual;

TO_CHAR(ROUND(TO_DATE(SYSD
--------------------------
05-APR-2013 13:00

SQL>
SQL> alter session set nls_date_Format = 'DD-MM-RRRR HH24:MI';

Session altered.

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

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') from dual;

TO_CHAR(ROUND(TO_DATE(SYSD
--------------------------
05-APR-2013 14:00

SQL>
SQL> alter session set nls_date_Format = 'DD-MM-RRRR HH24:MI:SS';

Session altered.

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

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') from 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

[Jonathan Lewis]

"Syltrem" <syltr...@videotron.ca> wrote in message
news:ii8ul8tnjr5ejjf3u...@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 think your observations make sense when you consider that
to_date(sysdate) means to_date(to_char(sysdate)), which means
to_date(to_char(sysdate,'dd-MON-yyyyy')) until you set the nls_date_format,
and then it means to_date(to_char(sysdate,'{your nls_date_format}')).


--

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

[Syltrem]

On Fri, 5 Apr 2013 21:24:34 +0100, "Jonathan Lewis"
<jona...@jlcomp.demon.co.uk> wrote:

>
>"Syltrem" <syltr...@videotron.ca> wrote in message
>news:ii8ul8tnjr5ejjf3u...@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 think your observations make sense when you consider that
>to_date(sysdate) means to_date(to_char(sysdate)), which means
>to_date(to_char(sysdate,'dd-MON-yyyyy')) until you set the nls_date_format,
>and then it means to_date(to_char(sysdate,'{your nls_date_format}')).

Thanks for your reply
Indeed I think you gave a correct explanation

Merci
Syltrem