ORA-01830: date format picture ends before converting entire input string

Posted on 2009-04-16
Last Modified: 2013-12-19
Update table set DISAPPROVED_DATETIME = to_date('" & DateTime.Now & "','mm/dd/yyyy')  where emp_no= 123

i am getting this exeception when i tried to execute this isql....the disapproved_datetime is of datatype "Date"
Question by:yuvaratna
    LVL 31

    Expert Comment

    This, '" & DateTime.Now & "', must not be like 04/16/2009.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    this should work better:
    Update table set DISAPPROVED_DATETIME = to_date('" & DateTime.Now.ToString("mm/dd/yyyy") & "','mm/dd/yyyy')  where emp_no= 123

    Open in new window

    LVL 31

    Expert Comment

    angelIII's solution should do the trick because it forces the Vb Now() function and the Oracle to_date function to use the same format. Good job.
    LVL 142

    Accepted Solution

    note: if you really are just interested in the "now" or "today" value, you can also use the oracle function sysdate:

    Update table set DISAPPROVED_DATETIME = SYSDATE; ---> now (including time)
    Update table set DISAPPROVED_DATETIME = TRUNC(SYSDATE);  ---> today 0:00:00'

    Open in new window


    Author Closing Comment

    Thanks a lot!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to recover a database from a user managed backup

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now