0

I have been running into a weird issue while using SQL DEVELOPER, I have two dates that I would like to get the difference between; however, I have been getting inaccurate results...

I have:

 column1 with the date 03-Aug-2016
 column2 with the date 08-July-2016

when using to_date function with the following:

to_date(column1, 'dd-mon-yyyy') ==> the date is displayed as 03-Aug-2016

but column2 is different story as it gives the following:

to_date(column2, 'dd-mon-yyyy') ==> the date is displayed as 10-July-2016

but when using to_date(column2, 'dd-mon-RR') ==> the date is displayed as 08-July-2016

and when I use the following in my query I get a weird number

to_date(column1, 'dd-mon-yyyy') - to_date(column2, 'dd-mon-RR') ==> -730463

Your help is really appreciated!

Improve this question
2
  • what is the datatype of column1 and column2? . If it's already date, you should not be applying to_date over it. Commented Oct 5, 2018 at 12:26
  • Both are dates; however, when I apply the subtraction it gives -730463.4 Commented Oct 5, 2018 at 12:29

2 Answers 2

Reset to default
1

The problem is that you are trying to convert a column which is already a date using TO_DATE

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.

Since you have provided the first argument as a DATE type, Oracle will implicitly convert it to a string using your session's NLS parameters.

So,to_date(column2, 'dd-mon-yyyy') is equivalent to

SELECT TO_DATE(TO_CHAR(DATE '2016-07-08', ( -- 08-July-2016
     SELECT value
     FROM nls_session_parameters
     WHERE parameter = 'NLS_DATE_FORMAT' 
) ),'dd-mon-yyyy')
FROM dual

10-jul-16 is what you are seeing from the above(again due to your session's NLS format), but it is actually 10-jul-0016 and not 10-Jul-2016

Whereas the expression to_date(column2, 'dd-mon-RR') is evaluating to 08-jul-2016. This is because your NLS_DATE_FORMAT is set to 'DD-MON-RR' if I'm not wrong.

So, the weird number you see is the difference between the two dates.

If you want to confirm, simply run this. i.e. subtracting date literals as pure dates without conversion.

SELECT DATE '0016-08-03' - DATE '2016-07-08' FROM dual;--  -730461
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thanks! I see, and I tried subtracting directly since they are already in date format; however, the number still is not shown in days.
@user2597012 : What do you see then?
I see -730463, that is what is displayed
Ok, then that means it is stored incorrectly ?. what do you see when you run select to_char(column1,'yyyy-mm-dd') , to_char(column2,'yyyy-mm-dd') ..? Also, what's the o/p from SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'
I get 0016-08-01 and 2016-07-08.
|
0

Have you tried using the 

to_date(column1, 'dd-mon-RRRR') - to_date(column2, 'dd-mon-RRRR')

Alway use RRRR when you are working on dates. this will ensure to have the dates format as per y2k.

If this doesn't solve your issue, Please share the column types you are having for these Column1 and Column2

Improve this answer

2 Comments

Thanks for your help. I tried it and it gave me 24 days (where it should be 26) but as column1 result was 1-AUG-2016 instead of 3rd. and both columns have "DATE" and datatype.
if both your columns are a date then simple use Column1-column2 why to recast these to dates. If you want to left out time then use Trunc(column1)-trunc(column2)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.