Home » SQL & PL/SQL » SQL & PL/SQL » help required with date and time inserts (oracle 10g)
help required with date and time inserts [message #437551] Tue, 05 January 2010 00:23 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi iam inserting three type od records with date and time not using am and pm.

my inserts are like this.

The following is for time insert: 


INSERT INTO EMROperativeNotes( OPERATIVE_NOTES_ID, SURGERY_START_DATETIME) 
values (2101501, to_date('05:00 pm','hh:mi')

The following is for date insert:

INSERT INTO EMROperativeNotes( OPERATIVE_NOTES_ID, SURGERY_START_DATETIME) 
values (2101502, to_date('05-01-2010','dd-mon-yyyy')

The following for date and time insert: 

INSERT INTO EMROperativeNotes( OPERATIVE_NOTES_ID, SURGERY_START_DATETIME) 
values 2101503,to_date( '05-01-2010 02:00 pm','dd-mon-yyyy hh:mi')

[Updated on: Tue, 05 January 2010 01:38] by Moderator

Report message to a moderator

Re: help required with date and time inserts [message #437553 is a reply to message #437551] Tue, 05 January 2010 00:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
So what is the problem?

regards,
Delna
Re: help required with date and time inserts [message #437554 is a reply to message #437553] Tue, 05 January 2010 00:34 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
all am getting invalid month for the above queries
Re: help required with date and time inserts [message #437555 is a reply to message #437554] Tue, 05 January 2010 00:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Provide working test case, with CREATE and INSERT statements (which throws an error).

regards,
Delna
Re: help required with date and time inserts [message #437556 is a reply to message #437555] Tue, 05 January 2010 00:43 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
iam inserting the queries through application as am or pm and it should retrieve in the same way as am or pm.that is where am trying
Re: help required with date and time inserts [message #437557 is a reply to message #437556] Tue, 05 January 2010 00:47 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Provide working test case, with CREATE and INSERT statements (which throws an error).


regards,
Delna
Re: help required with date and time inserts [message #437558 is a reply to message #437555] Tue, 05 January 2010 00:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
delna.sexy wrote on Tue, 05 January 2010 12:06
Provide working test case, with CREATE and INSERT statements (which throws an error).

regards,
Delna


But Why...

Quote:
all am getting invalid month for the above queries

i.e.
ORA-01843: not a valid month
.

He knows the answer for that By just re read his previous posts.

Rajasekhar Please read .the documents
Quote:
ORA-01843: not a valid month
Cause: A date specified an invalid month. Valid months are: January-December, for format code MONTH, and Jan-Dec, for format code MON.
Action: Enter a valid month value in the correct format.
Re: help required with date and time inserts [message #437565 is a reply to message #437556] Tue, 05 January 2010 01:07 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
SQL>desc t14;
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------
 COL1                                                                                         DATE

SQL>insert into t14 values(to_date('05:00 pm','hh:mi');
insert into t14 values(to_date('05:00 pm','hh:mi')
                                                 *
ERROR at line 1:
ORA-00917: missing comma


SQL>insert into t14 values(to_date('05:00 pm','hh:mi'));
insert into t14 values(to_date('05:00 pm','hh:mi'))
                               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Now, why don't you add AM or PM in format mask?

SQL>insert into t14 values(to_date('05-01-2010','dd-mon-yyyy'));
insert into t14 values(to_date('05-01-2010','dd-mon-yyyy'))
                               *
ERROR at line 1:
ORA-01843: not a valid month


Do the date specified and format mask match?

regards,
Delna
Re: help required with date and time inserts [message #437566 is a reply to message #437565] Tue, 05 January 2010 01:10 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
With out executing the queries also we can say the error by observing the to_date() format.right?

./fa/3943/0/

Please Don`t spoon feed.
./fa/1707/0/

sriram Smile

[Updated on: Tue, 05 January 2010 01:12]

Report message to a moderator

Re: help required with date and time inserts [message #437583 is a reply to message #437566] Tue, 05 January 2010 02:25 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
Please Don`t spoon feed.


I just pointed out mistakes so that OP can get hints. Didn't do spoon feeding.

regards,
Delna
Re: help required with date and time inserts [message #437596 is a reply to message #437551] Tue, 05 January 2010 03:19 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
@rajasekhar857,

this is the problem (in a case you didn't understand the error message explanation):

to_date('05-01-2010','dd-mon-yyyy')

"MON" date format mask expects three letters for a month (such as JAN, FEB, MAR, ..., DEC). Your input value contains two digit number month representation (01, 02, ..., 12), which expects the "MM" format mask.

So, either use
to_date('05-01-2010','dd-mm-yyyy')
or
to_date('05-jan-2010','dd-mon-yyyy')
if you want to get rid of invalid month.
Re: help required with date and time inserts [message #437605 is a reply to message #437566] Tue, 05 January 2010 03:29 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
So ramoradba, do you want to say anything, after Littlefoot sir's post?

regards,
Delna
Re: help required with date and time inserts [message #437606 is a reply to message #437605] Tue, 05 January 2010 03:39 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Yes... It's not an argument or debate to say/counter after every post. (Or to increase the no of posts)./fa/2866/0/

If the OP really want the solution not the hint then you guys are correct.
i mean More than 400 posts he is not a new one for this forum/Oracle If the OP does not know about to_date() format even, is this proper to teach him like that ?

If you (members) think that is OK then i have no objection please go ahead with all the member same like this.

You asked him about to post test case did you get that test case?

My point is....
It is better to give him a hint not solution...As per the forum guide lines First we should give the hint then if user/OP comes with any error/unable to get the hint then only give the solution.


Hope this is clear enough. If you (members) find any thing wrong in this let me know.



sriram Smile

[Updated on: Tue, 05 January 2010 03:50]

Report message to a moderator

Re: help required with date and time inserts [message #437610 is a reply to message #437606] Tue, 05 January 2010 03:55 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
If the OP does not know about to_date() format even,

If OP is aware of to_date() properly, then he/she wouldn't ask this question here. Laughing

Quote:
As per the forum guide lines First we should give the hint then if...

Where did you find me giving solution in this thread? ./fa/1600/0/

regards,
Delna
Re: help required with date and time inserts [message #437656 is a reply to message #437606] Tue, 05 January 2010 06:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Please stop this and leave the moderating to the moderators.
There is no need for even more people telling others what they can and cannot do (in their personal view)
Re: help required with date and time inserts [message #437820 is a reply to message #437656] Wed, 06 January 2010 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I fully support this advice. There has been (IMHO) far too many posts lately where people are simply criticising other people posts.

This forum has moderators - if a post offends you, or you believe it contravenes forum guide lines, use the 'Report This Post' button to bring it to our attention. If we agree with you, then we'll do something about it.

Re: help required with date and time inserts [message #437822 is a reply to message #437820] Wed, 06 January 2010 02:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
JRowbottom sir,
Quote:
There has been (IMHO) far


IMHO stands for...?

regards,
Delna
Re: help required with date and time inserts [message #437824 is a reply to message #437822] Wed, 06 January 2010 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Age old Internet slang.
In My Humble Opinion
Re: help required with date and time inserts [message #437825 is a reply to message #437822] Wed, 06 January 2010 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In My Humble Opinion.

http://www.acronymslist.com/

Regards
Michel
Re: help required with date and time inserts [message #437826 is a reply to message #437824] Wed, 06 January 2010 03:08 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
./fa/1587/0/

regards,
Delna
Previous Topic: how to compare the columns and return the value thorugh a query? (merged 3)
Next Topic: Doubt (merged)
Goto Forum:
  


Current Time: Wed Dec 07 08:40:44 CST 2016

Total time taken to generate the page: 0.12753 seconds