Home/ Questions/How to avoid the error "date format picture ends before converting entire input string"?
Next
Answered
Nathan Normand
  • 22
Asked: 2022-05-18T10:50:18+00:00 In:

How to avoid the error “date format picture ends before converting entire input string”?

  • 22

. Advertisement .

..3..

. Advertisement .

..4..

Hi developer experts, I have a small but frustrating use case, and so far, I couldn’t get my head around this problem & ideal solution. I am running my command and facing one problem with the date format picture ends before converting entire input string. Below is the command I used:

insert into visit
 values(123456, '19-JUN-13', '13-AUG-13 12:56 A.M.');

When I run it, I get the following error:

date format picture ends before converting entire input string

I am looking forward to gaining some knowledge from all experts. Thank you, guys!

date format picture
Share

2 Answers

  1. Best Answer
    Expert

    The cause:

    This error happens perhaps due to the valid date format picture including extra data. I think what you are trying to insert is not a date, it is a string. The first part of the format picture is changed into a valid date, but the remaining data is not requested.

    Solution:

    To fix this error, you have to check NLS_DATE_FORMAT, then use the date string that fits with the format.

    Another way is using the to_date function in the INSERT sentence, such as the following:

    insert into visit
values(123456, 
 to_date('19-JUN-13', 'dd-mon-yy'),
 to_date('13-AUG-13 12:56 A.M.', 'dd-mon-yyyy hh:mi A.M.'));

    Date and time informattions are also stored together in Oracle DATE.

    • 8

  2. You will need to change the session

    You can also try it before you insert 

     sql : alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
    • 14

Leave an answer

You must login to add an answer.

Need An Account,