1

I'm having this error on trying update my database trough Oracle.ManagedDataAccess.Client in C#. ORA-01830: date format picture ends before converting entire input string

I've already readed those posts:

ORA-01830: date format picture [duplicate] and ORA-01830: date format picture ends before converting entire input string / Select sum where date query

But they actually didn't work for me. When I ran this in SQL Navigator for Oracle in a Select, worked as a charm, but while calling to the update below from .Net it throws me this error.

update tbUser
   set dt_inactive =
          case
             when trim(to_date(:dt_inactive, 'dd/mm/yyyy HH24:MI:SS')) is not null
                then to_date(:dt_inactive, 'dd/mm/yyyy HH24:MI:SS')
             else
                to_date(dt_inactive, 'dd/mm/yyyy HH24:MI:SS')
          end
 where user_code = 'DEV01'

Debugging in Visual Studio, I've checked that the date comes like this: :dt_inactive = 30/01/2017 14:05:25, and that is exactly the format I want to send to to my database. However the error persists.

I've already tried to send this:

TO_DATE(:dt_inactive, 'dd/mm/yyyy HH:MI:SS') -- Throws me ORA-01849: hour must be between 1 and 12
TO_DATE(:dt_inactive, 'dd/mm/yyyy hh24:mi:ss')
TO_DATE(:dt_inactive, 'dd/mm/yyyy HH24:MI:SS')
TO_DATE(TO_CHAR(:dt_inactive), 'dd/mm/yyyy hh24:mi:ss')
TO_DATE(TO_CHAR(:dt_inactive), TO_CHAR('dd/mm/yyyy hh24:mi:ss'))

But nothing seem to work.

Create a procedure is not an option.

Any help would be appreciated.

Community
  • 1
  • 1
Elek Guidolin
  • 487
  • 1
  • 8
  • 21
  • Please provide some sample dates that you wanted to update. – atokpas Jan 31 '17 at 03:59
  • @JSapkota I'm afraid I didn't understand your comment. The update is in the post. As well the value to the parameter .. But any date is returning the same error. 31/01/207 18:15:25, 25/01/2017 13:45:15, and so on. – Elek Guidolin Jan 31 '17 at 11:32
  • Yes @JSapkota, this field is a Date in the database. I sent like this: Case When Trim(TO_CHAR(:dt_inactive)) IS NOT NULL Then TO_CHAR(:dt_inactive, 'DD/MM/YYYY HH24:MI:SS') Else TO_CHAR(dt_inactive, 'DD/MM/YYYY HH24:MI:SS') End, and the error is still here. – Elek Guidolin Jan 31 '17 at 11:49

1 Answers1

0

The error, 'ORA_01830', occurs when there is no format string for the date provided in the function. For example, in the following code, there is no format handler for Time Zone offset so we got the error.

According to your information the date has no time zone information but I suspect the date that you are providing is not correct. 

SQL> select to_Date('06/10/11 15:24:16 +00:00', 'dd/mm/yyyy hh24:mi:ss') from dual;
select to_Date('06/10/11 15:24:16 +00:00', 'dd/mm/yyyy hh24:mi:ss') from dual
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

If it contains the time zone then you can use DateTimeOffset.Parse method to remove it.

atokpas
  • 3,231
  • 1
  • 11
  • 22