CODEWITHSUNDEEP
oracle-database
yatinbc
yatinbc

Reputation: 625

invalid number error for date difference

I want calculate difference in two dates and round that to 4 decimal. But following query gives me error as invalid number. What is wrong

SELECT ROUND((TO_DATE(to_char('26-02-2016 12:44:00','DD/MM/YY HH24:MI'),'DD/MM/YY HH24:MI') - TO_DATE(to_char('26/02/2016 12:38','DD/MM/YY HH24:MI'),'DD/MM/YY HH24:MI')), 4) 
from dual;

Upvotes: 1

Views: 927

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Don't call to_char on a string (just as you shouldn't call to_date on a date). Just call to_date to convert the string into a date. Your format masks also need to match the string-- you're using 4 digit years so use YYYY in the format mask and you either want to remove the seconds from the first string or add seconds to your format mask.

SELECT ROUND( to_date( '26-02-2016 12:44','DD-MM-YYYY HH24:MI') -
                to_date( '26/02/2016 12:38','DD/MM/YYYY HH24:MI'),
              4 )
  FROM dual

Upvotes: 1

Related Questions