not valid month in oracle date time

Question

0.00/5 (No votes)

See more:

SQL

Insert into PEPSIRENDEEMHISTORY(PIN,STATUS,CURRENTDATE,MSISDN,USERNAME,SIM)
VALUES ('4501664170',1,'5/12/2013 8:05:26 PM','2348121846632','ranjith-PC\ranjith',1)

Problem with date time while inserting showing not valid month

Posted 12-May-13 9:57am

Ranjith Kumar

Updated 12-May-13 10:14am

Maciej Los

v2

Add a Solution

3 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)

**Zoltán Zörgő** · Answer 1 · 2013-05-12T10:19:00

Solution 1

Check this: http://www.techonthenet.com/oracle/questions/insert_date.php[^]

Posted 12-May-13 10:19am

Zoltán Zörgő

Comments

Maciej Los 12-May-13 16:24pm

Great! +5

Zoltán Zörgő 12-May-13 16:24pm

Thanks :)

Ranjith Kumar 12-May-13 16:33pm

CAN U SHARE SOME SAMPLE EXAMPLES PLZ THE DATEFORMAT SHOULD BE IN MM/DD/YYYY EXAMPLE 05/12/2013

Maciej Los 12-May-13 16:35pm

Did you try to change it at all?

Zoltán Zörgő 12-May-13 16:36pm

Don't shout = don't use all caps!
There you have the whole list: http://www.techonthenet.com/oracle/functions/to_date.php
But mm/dd/yyyy is a valid format. You just need to specify the format.

Ranjith Kumar 12-May-13 16:38pm

i have tried its working thank you but i have one more problem...showing me cannot insert null value into serial which is auto increment column

Zoltán Zörgő 12-May-13 16:40pm

That's an other thing. Please open an other topic.

Ranjith Kumar 12-May-13 16:40pm

here is the schema
CREATE TABLE HAIER.PEPSIRENDEEMHISTORY
(
SERIAL NUMBER NOT NULL,
PIN VARCHAR2(50 BYTE),
STATUS NUMBER DEFAULT 0,
CURRENTDATE DATE,
MSISDN VARCHAR2(50 BYTE),
USERNAME VARCHAR2(50 BYTE),
SIM INTEGER DEFAULT 0
)

Zoltán Zörgő 12-May-13 16:44pm

Quite clear: PEPSIRENDEEMHISTORY has a field named SERIAL, and you haven't used it in the insert statement thus NULL is used, but you added a not null constraint.

Ranjith Kumar 12-May-13 16:45pm

Serial is primary key and auto increment

Zoltán Zörgő 12-May-13 17:11pm

And how exactly have you made it autoincrement? Check this: http://jen.fluxcapacitor.net/geek/autoincr.html
Still this is an other topic. You got your answer to the original question!

Ranjith Kumar 12-May-13 17:21pm

thank you let me check plzzz

Member 10351768 · Answer 2 · 2013-10-22T21:51:00

Solution 2

Get That in in your java code.and use SimpleDateFormat class specify the format of ur date format then convert that one into util date format then convert that one into timestamp format then it will inserted into database with date and time

Posted 22-Oct-13 21:51pm

Member 10351768

Singh Gyan · Answer 3 · 2013-10-22T21:58:00

use syntax like this.

to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss')

for you its like

SQL

Insert into PEPSIRENDEEMHISTORY(PIN,STATUS,CURRENTDATE,MSISDN,USERNAME,SIM)
VALUES ('4501664170',1,to_date('2011/04/22 08:30:00', 'yyyy/mm/dd hh24:mi:ss'),'2348121846632','ranjith-PC\ranjith',1)

-SG