Home » SQL & PL/SQL » SQL & PL/SQL » Date Difference
Date Difference [message #276571] Thu, 25 October 2007 09:16 Go to next message
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.

Re: Date Difference [message #276578 is a reply to message #276571] Thu, 25 October 2007 09:44 Go to previous messageGo to next message
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.
Re: Date Difference [message #276579 is a reply to message #276571] Thu, 25 October 2007 09:44 Go to previous messageGo to next message
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
Re: Date Difference [message #276632 is a reply to message #276579] Thu, 25 October 2007 12:13 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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>
Re: Date Difference [message #276761 is a reply to message #276571] Fri, 26 October 2007 05:14 Go to previous messageGo to next message
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.
Re: Date Difference [message #276768 is a reply to message #276761] Fri, 26 October 2007 05:30 Go to previous messageGo to next message
ThomasG
Messages: 3189
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.
Re: Date Difference [message #276770 is a reply to message #276571] Fri, 26 October 2007 05:34 Go to previous messageGo to next message
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.

Re: Date Difference [message #276771 is a reply to message #276768] Fri, 26 October 2007 05:37 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Date Difference [message #276772 is a reply to message #276770] Fri, 26 October 2007 05:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
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
Re: Date Difference [message #276776 is a reply to message #276771] Fri, 26 October 2007 05:46 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 Go to previous messageGo to next message
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.
Re: Date Difference [message #276790 is a reply to message #276783] Fri, 26 October 2007 06:03 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: Date Difference [message #276795 is a reply to message #276783] Fri, 26 October 2007 06:03 Go to previous messageGo to next message
pablolee
Messages: 2835
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
Re: Date Difference [message #276799 is a reply to message #276776] Fri, 26 October 2007 06:08 Go to previous messageGo to next message
Maaher
Messages: 7062
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

@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
Re: Date Difference [message #276951 is a reply to message #276571] Sat, 27 October 2007 04:44 Go to previous messageGo to next message
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.
Re: Date Difference [message #276954 is a reply to message #276951] Sat, 27 October 2007 04:51 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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;
Re: Date Difference [message #276955 is a reply to message #276571] Sat, 27 October 2007 05:05 Go to previous messageGo to next message
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.

Re: Date Difference [message #276963 is a reply to message #276955] Sat, 27 October 2007 08:41 Go to previous messageGo to next message
Frank
Messages: 7880
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!
Re: Date Difference [message #277037 is a reply to message #276571] Sun, 28 October 2007 03:13 Go to previous messageGo to next message
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 ?

Re: Date Difference [message #277042 is a reply to message #277037] Sun, 28 October 2007 03:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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
Re: Date Difference [message #277073 is a reply to message #276571] Sun, 28 October 2007 08:15 Go to previous messageGo to next message
Kprattip
Messages: 15
Registered: January 2006
Location: INDIA
Junior Member
You are absolutely right.

Thanks.
Re: Date Difference [message #277319 is a reply to message #276571] Mon, 29 October 2007 09:42 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: Create dynamic table_Name
Next Topic: Problems reducing a selection of grouped records
Goto Forum:
  


Current Time: Fri Dec 09 04:07:18 CST 2016

Total time taken to generate the page: 0.09321 seconds