OraFAQ Forum: SQL & PL/SQL » Date Difference

Home » SQL & PL/SQL » SQL & PL/SQL » Date Difference

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Difference [message #276571]

Thu, 25 October 2007 09:16

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hi,

Can any one tell me what could be the reason for the below begaviour of date differences:

SQL> select sysdate - to_date('10-JUN-2007','DD-MON-YYYY') from dual;

SYSDATE-TO_DATE('10-JUN-2007','DD-MON-YYYY')
--------------------------------------------
137.29838

This is as expected

SQL> select (to_date(sysdate,'DD-MON-YYYY') - to_date('10-JUN-2007','DD-MON-YYYY')) from dual;

(TO_DATE(SYSDATE,'DD-MON-YYYY')-TO_DATE('10-JUN-2007','DD-MON-YYYY'))
---------------------------------------------------------------------
-730350
Sysdate: 25-OCT-2007

Here sysdate is greater than JUN'2007.

So want to know whats the reason fr this ?

Is it conerting the date to char of sysdate.

Report message to a moderator

Re: Date Difference [message #276578 is a reply to message #276571]

Thu, 25 October 2007 09:44

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

What you are doing is nonsensical. You are applying TO_DATE to something which is already a DATE. You would need to do a TO_CHAR on SYSDATE before applying the TO_DATE, but this would be completely pointless.

Report message to a moderator

Re: Date Difference [message #276579 is a reply to message #276571]

Thu, 25 October 2007 09:44

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

SQL> select sysdate - to_Date('10-jun-2007','dd-mon-yyyy') from dual;

SYSDATE-TO_DATE('10-JUN-2007',
------------------------------
                    137.652986


  1* select (to_date(sysdate,'dd-mon-yyyy') - to_date('10-JUN-2007','DD-MON-YYYY')) from dual
SQL> /

(TO_DATE(SYSDATE,'DD-MON-YYYY'
------------------------------
                       -730350


SQL> select to_Date('25-oct-0007','dd-mon-yyyy') - to_Date('10-jun-2007','dd-mon-yyyy') from dual
  2  ;

TO_DATE('25-OCT-0007','DD-MON-
------------------------------
                       -730350


SQL> alter session set nls_Date_format = 'DD-MON-YYYY';

Session altered.

SQL> select sysdate - to_Date('10-jun-2007','dd-mon-yyyy') from dual;

SYSDATE-TO_DATE('10-JUN-2007',
------------------------------
                    137

SQL> select (to_date(sysdate,'dd-mon-yyyy') - to_date('10-JUN-2007','DD-MON-YYYY')) from dual
  2  /

(TO_DATE(SYSDATE,'DD-MON-YYYY'
------------------------------
                           137

Next time please format your post and read the guidelines before you post.

Thanks

Raj

Report message to a moderator

Re: Date Difference [message #276632 is a reply to message #276579]

Thu, 25 October 2007 12:13

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Yet another explanation: I guess it is time portion of the SYSDATE function that confuses you. Depending on default NLS settings, SYSDATE will return only a date (and you won't see time at all). You may, however, see how it really looks like by altering a session or applying the TO_CHAR function to SYSDATE:

SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate from dual;

SYSDATE
-------------------
25.10.2007 19:10:09

SQL> select to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss') today from dual;

TODAY
-------------------
25.10.2007 19:10:23

When you subtract SYSDATE and another date, you'll have to take care about time. In this example you will NOT get days as a result - there'll be time as well:

SQL> select sysdate - to_date('10.06.2007', 'dd.mm.yyyy') result from dual;

    RESULT
----------
137.796493

But, if you TRUNCATE sysdate (i.e. remove time), you'll get only days:

SQL> select trunc(sysdate) today from dual;

TODAY
-------------------
25.10.2007 00:00:00

SQL> select TRUNC(sysdate) - to_date('10.06.2007', 'dd.mm.yyyy') result
  2  from dual;

    RESULT
----------
       137

SQL>

Report message to a moderator

Re: Date Difference [message #276761 is a reply to message #276571]

Fri, 26 October 2007 05:14

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hi All,

This is not at all what I wanted.

These NLS setting does not effect the client.
It does effect the server depending on the values in v$parameter file.

Now, suppose if my sql as below runs at the server,

SQL> select to_date(sysdate,'DD-MON-YYYY HH:MI:SS') - to_date('20-JUN-2007 12:09:50','DD-MON-YYYY HH
:MI:SS') from dual;

TO_DATE(SYSDATE,'DD-MON-YYYYHH:MI:SS')-TO_DATE('20-JUN-200712:09:50','DD-MON-YYYYHH:MI:SS')
-------------------------------------------------------------------------------------------
-730359.5

does it give the above result or the correct one.

Because for every sql we do not join with V$parameter for the NLS_DATE_FORMAT.

(or) should we always join with v$parameter to get the correct data format.

Thanks,
Kiran.

Report message to a moderator

Re: Date Difference [message #276768 is a reply to message #276761]

Fri, 26 October 2007 05:30

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

The only thing you should should do is :

ALWAYS convert a VARCHAR to DATE with to_date() and the correct format picture that is in the varchar when you use it, so you get the right data type.

NEVER convert a DATE to DATE with to_date(), since it will make absolutely no sense to do it.

Report message to a moderator

Re: Date Difference [message #276770 is a reply to message #276571]

Fri, 26 October 2007 05:34

Cthulhu
Messages: 381
Registered: September 2006
Location: UK

Senior Member

Exactly what I said. I'm totally bewildered why the OP is so determined to pursue a completely bogus topic.

Report message to a moderator

Re: Date Difference [message #276771 is a reply to message #276768]

Fri, 26 October 2007 05:37

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Wise words from Thomas. If you listen to him, you won't be needing V$PARAMETER. You wouldn't find the date format there anyway. V$NLS_PARAMETERS is the one you meant (at least on our 9i and 10g boxes).

MHE

Report message to a moderator

Re: Date Difference [message #276772 is a reply to message #276770]

Fri, 26 October 2007 05:38

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Quote:

Exactly what I said. I'm totally bewildered why the OP is so determined to pursue a completely bogus topic.

He might be one of those people who only believe things when they are screamed into their ears for one hour by at least 5 people, so I came to your aid. Wink

Wink

Report message to a moderator

Re: Date Difference [message #276776 is a reply to message #276771]

Fri, 26 October 2007 05:46

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Maarten,
It is also there in v$parameter. But the names are in small letters. At lest I could see nls_date_format.
Some of them are not having values.
May be V$NLS_PARAMETERS is the correct thing to look at.

By
Vamsi

[Updated on: Fri, 26 October 2007 05:46]

Report message to a moderator

Re: Date Difference [message #276783 is a reply to message #276571]

Fri, 26 October 2007 05:55

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hey,

Thanx dear...

Very Heated words from lot many...

some says shouting.... some says... 1 hour shouting...

I had seen varied number of answers and not only one answer to react with...

So senior members of this forum...

always remember basic funda 'please think in others perspective before u act upon....'.

Bye n Thnq for ur valuable time on this.

Kiran.

Report message to a moderator

Re: Date Difference [message #276790 is a reply to message #276783]

Fri, 26 October 2007 06:03

Frank
Messages: 7901
Registered: March 2000

Senior Member

Look, you asked a question.
People pointed you to the fact that you should not do a to_date(sysdate)
You refused to accept that stating "that is not what I want"
There is no "other's perspective" in this. It is the way Oracle works.

Quote:

Bye n Thnq for ur valuable time on this.

And please, take the effort to type complete words.

Report message to a moderator

Re: Date Difference [message #276795 is a reply to message #276783]

Fri, 26 October 2007 06:03

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Hi Kprattip.
I am confused by what you are saying. Can you translate some stuff for me?

What is funda?
What does Thanx mean?
Who is u?
What does Thnq mean?
Who is ur?

What does this mean

Quote:

always remember basic funda 'please think in others perspective before u act upon....'.

Thank you

Report message to a moderator

Re: Date Difference [message #276799 is a reply to message #276776]

Fri, 26 October 2007 06:08

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Drat! I spoke too soon. You're right, Vamsi. Apparently v$parameter does contain the nls_date_format. Another lesson learnt. Very Happy

Very Happy

@OP:
1. There were no "heated" words: just advice you refuse to listen to.
2. This forum has adopted ENGLISH as it's main language, not jibberish.
3. Your program is bound to fail at some point if insist on ignoring the advice given to you.

MHE

Report message to a moderator

Re: Date Difference [message #276951 is a reply to message #276571]

Sat, 27 October 2007 04:44

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hi friends,

I have a point to make here.

converting a to_number of a number value does not cause harm.
In the same way why does converting a date again to a date should cause a problem.

I remember that,
"ALWAYS convert a VARCHAR to DATE with to_date() and the correct format picture that is in the varchar when you use it, so you get the right data type.
NEVER convert a DATE to DATE with to_date(), since it will make absolutely no sense to do it."

I got actually whats the problem with converting a sysdate to a date using to_date, the format uses 'YYYY' and wrongly interprets 07 and 0007 and hence negative value.

so, its better to always use 'RRRR', while date conversions.

Lessons Learnt: Without respect to 'NLS_DATE_FORMAT' in V$parameter or V$NLS_PARAMETERS we can use the date format, and it should be having 'RRRR' or 'RR' format.

And also though we have some basic principles like
"ALWAYS convert a VARCHAR to DATE with to_date() and the correct format picture that is in the varchar when you use it, so you get the right data type.
NEVER convert a DATE to DATE with to_date(), since it will make absolutely no sense to do it."

There is no harm in converting a date to a date again.

SQL> select to_date(sysdate,'DD-MON-RRRR') - to_date('20-JUN-2007 12:09:50','DD-MON-YYYY HH:MI:SS')
from dual;

TO_DATE(SYSDATE,'DD-MON-RRRR')-TO_DATE('20-JUN-200712:09:50','DD-MON-YYYYHH:MI:SS')
-----------------------------------------------------------------------------------
128.49317

(OR)

SQL> select to_date(sysdate,'DD-MON-RR') - to_date('20-JUN-2007 12:09:50','DD-MON-YYYY HH:MI:SS') fr
om dual;

TO_DATE(SYSDATE,'DD-MON-RR')-TO_DATE('20-JUN-200712:09:50','DD-MON-YYYYHH:MI:SS')
---------------------------------------------------------------------------------
128.49317

Thanks for all ur replies.
Kiran.

Report message to a moderator

Re: Date Difference [message #276954 is a reply to message #276951]

Sat, 27 October 2007 04:51

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

There is no harm in converting a date to a date again.

If you say so ... but, can you explain where's the point in doing so? Why would you do something that useless?

Do you suggest that we should do a simple arithmetic like this:

SELECT TO_NUMBER(TO_NUMBER(100) - TO_NUMBER(50)) FROM dual;

Report message to a moderator

Re: Date Difference [message #276955 is a reply to message #276571]

Sat, 27 October 2007 05:05

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hi,

I mean there is no harm to the value of the output.
Not for the harm of the performance.

I do agree converting a date to a date again is useless.

Thanks.

Report message to a moderator

Re: Date Difference [message #276963 is a reply to message #276955]

Sat, 27 October 2007 08:41

Frank
Messages: 7901
Registered: March 2000

Senior Member

Converting a date to a date, the way it was done in the original post DOES harm.
The to_date had a format mask. That means that there will be an error if the format mask that is used for the implicit to_char differs!

Report message to a moderator

Re: Date Difference [message #277037 is a reply to message #276571]

Sun, 28 October 2007 03:13

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

Hi Frank,

I did not understand "That means that there will be an error if the format mask that is used for the implicit to_char differs! "

Can u tell me what doe sit mean, what could be the implici format mask for to_char if being converted to date, Is it NLS_DATE_FORMAT ?

Report message to a moderator

Re: Date Difference [message #277042 is a reply to message #277037]

Sun, 28 October 2007 03:25

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In the original post of this topic, you selected

SELECT TO_DATE(SYSDATE, 'DD-MON-YYYY') FROM dual;

It will throw an error if the default NLS date format differs from 'dd-mon-yyyy'. See an example in my database:

SQL> select to_date(sysdate, 'dd-mon-yyyy') from dual;
select to_date(sysdate, 'dd-mon-yyyy') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

How come? Because NLS date format in my database is

SQL> select sys_context('userenv', 'nls_date_format') from dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
--------------------------------------------------------------------

DD.MM.RR

However, if you omit format mask, it will be OK but pointless (just as TO_NUMBER(100)) ('lis' = 'oct' in Croatian):

SQL> select to_date(sysdate) from dual;

TO_DATE(
--------
28.10.07

SQL> alter session set nls_date_Format = 'dd-mon-yyyy';

Session altered.

SQL> select to_date(sysdate) from dual;

TO_DATE(SYS
-----------
28-lis-2007

Report message to a moderator

Re: Date Difference [message #277073 is a reply to message #276571]

Sun, 28 October 2007 08:15

Kprattip
Messages: 15
Registered: January 2006
Location: INDIA

Junior Member

You are absolutely right.

Thanks.

Report message to a moderator

Re: Date Difference [message #277319 is a reply to message #276571]

Mon, 29 October 2007 09:42

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Quote:

In the same way why does converting a date again to a date should cause a problem.

SQL Reference
Chapter 5 Functions
TO_DATE

Quote:

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is J, for Julian, then char must be an integer.

As SYSDATE is returning DATE, it shall be first converted to VARCHAR2 (Oracle does it implicitely using NLS_DATE_FORMAT). So internally it is called as

SELECT TO_DATE( TO_CHAR( SYSDATE, <NLS_DATE_FORMAT>), 'DD-MON-YYYY' ) FROM dual;

Not only unnecessary complicated, but making your code dependant on NLS_DATE_FORMAT setting. Well, as you use MON in format, you are dependant on NLS_LANGUAGE, as Littlefood pointed out; but that is a little different issue.

Have a look at TO_NUMBER, the situation is similar there.

Report message to a moderator

Previous Topic:	Create dynamic table_Name
Next Topic:	Problems reducing a selection of grouped records

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 14:40:27 CDT 2024