CODEWITHSUNDEEP
sqloracle-databasedatedatetimeplsql
ikaln00
ikaln00

Reputation: 95

Why does converting date to_char() throw ORA-01830 error when placed in a case-then?

I have been using this code for a long time, and it works fine:
to_char(v_emailRow.first_stamp, 'dd.mm.yyyy')

However, today I needed to add the following condition
case when v_cardCode = 'C1' then v_date else to_char(v_emailRow.first_stamp, 'dd.mm.yyyy') end.

As soon as I added the case-then statement, whenever the to_char() needed to be executed, it began always throwing ORA-01830: date format picture ends before converting entire input string error. If I remove case-then, it works as usual, if I add it back, the error immediately returns.

I have tried writing a test script, googling, but I cannot find the issue. The same value is being converted in both cases, why does one of them work and the other throws an error?

Upvotes: 1

Views: 549

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

when v_cardCode = 'C1' then v_date else to_char(v_emailRow.first_stamp, 'dd.mm.yyyy')

whenever the to_char() needed to be executed, it began always throwing ORA-01830: date format picture ends before converting entire input string error

v_date, as the variable name suggests it's a DATE data type, while applying TO_CHAR converts date to a STRING. Therefore, having two different data types is causing the error.

Try below CASE in your SQL:

case 
  when v_cardCode = 'C1' 
  then to_char(v_date, 'dd.mm.yyyy')
  else to_char(v_emailRow.first_stamp, 'dd.mm.yyyy') 
end

Upvotes: 2

Related Questions