Error: insert / update date values into oracle DB

Question

1.00/5 (1 vote)

See more:

Below is the piece of the code (simplified) for updating a table w/ a DataTime value, where the field:

CREATED_DATE

is DATE in its DataType

SQL

Update  myTable SET CREATED_DATE=5/8/2017 9:17:07 AM WHERE E_ID = 9270002

I tried this in SQL Developer, but not successful. What is wrong in my code? Thanks.

What I have tried:

Tried on SQL Developer and also in .Net debugging

Posted 8-May-17 9:48am

s yu

Updated 8-May-17 10:21am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ZurdoDev · Accepted Answer · 2017-05-08T10:18:00

Solution 1

You need to put the values in quotes, otherwise it thinks you are trying to divide numbers.

SQL

SET CREATED_DATE='5/8/2017 9:17:07 AM'

Posted 8-May-17 10:18am

ZurdoDev

Comments

Maciej Los 8-May-17 16:21pm

5ed!

s yu 9-May-17 7:20am

Thank. But need to do like that one below:
SET CREATED_DATE='8-MAY-2017 9:17:07 AM'

CHill60 · Accepted Answer · 2017-05-08T10:21:00

Try

SQL

Update myTable SET CREATED_DATE='2017-05-08 09:17:07' WHERE E_ID=9270002

Note that the date cannot be interpreted as anything other than 8th May 2017. I also think if you are going to use "AM" in the time there should be no space. Personally I think 24hr format is best.

For more information see Understanding Oracle SQL Date Format Made Easy[^]

[UPDATE]OP is reporting an error.
Try '2017-MAY-08 09:17:07" instead. Or try

SQL

TO_DATE(
    'May 8, 2017, 09:17 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')

Reference:TO_DATE[^]
Otherwise look to the link I provided for more ideas