Getting Error with Date Format in To_Char

Question

I am getting error in the second script. Please explain why i am getting error in the second script.

select count(*) from LCL_SHR_IncidentIntegrationInt where externalsystem = 'IPSOFT' and (to_char(sysdate,'YYYYMMDDHH24MISS')-to_char(fn_adjusted_date(CREATE_DATE),'YYYYMMDDHH24MISS')) > 180;

O/P : 122797

select count(*) from LCL_SHR_IncidentIntegrationInt where externalsystem = 'IPSOFT' and (to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')-to_char(fn_adjusted_date(CREATE_DATE),'DD-MM-YYYY HH24:MI:SS')) > 180;

O/P : ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

You convert two dates to character strings and then try to subtract one of those character strings from the other. — Shannon Severance
What do you expect the mathematical expression of "27-10-2015 11:07:46" minus "27-10-2015 10:07:46" to result in? — Michael Broughton

Munir Munir · Accepted Answer · 2015-10-27T15:05:35

The first query works because Oracle is able to implicitly cast the characters as number and compare with 180. The other one doesn't because the : and - cannot be implicitly cast to number. You should use date and time functions for such cases like timestampdiff or datediff. More functions here - https://docs.oracle.com/cd/E17952_01/refman-5.1-en/date-and-time-functions.html

Getting Error with Date Format in To_Char

1 Answers