OraFAQ Forum: Client Tools » SQL PLUS TIMESTAMP

Home » Other » Client Tools » SQL PLUS TIMESTAMP

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL PLUS TIMESTAMP [message #393233]

Fri, 20 March 2009 15:31

namitanamburi
Messages: 35
Registered: March 2009

Member

Hi,

Iam new to sql plus.

There has been a new table created.

Here is the part of the DDL

"CREATED_DATE" TIMESTAMP NOT NULL

so the column name is CREATED_DATE and I donot know the format to insert this.

I tried typing this to know format of timestamp.

SQL> select property_name , property_value from database_properties where property_name='NLS_DATE_FORMAT' or property_name='NLS_TIMESTAMP_FORMAT' or property_name='NLS_TIMESTAMP_TZ_FORMAT';

Here is the result that I got

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

So I tried to insert 10-jun-08 12.09.43123 AM as in the format of

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

Then Iam getting an error

ERROR at line 1:
ORA-01855: AM/A.M. or PM/P.M. required

I tried all ways like A.M. or AM etc nothing worked.

Can anyone help me with this.

Also can anyone refer me to tutorials online for TOAD or SQL Plus.

Thanks
Namita.

Report message to a moderator

Re: SQL PLUS TIMESTAMP [message #393244 is a reply to message #393233]

Fri, 20 March 2009 16:03

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You should insert the TO_TIMESTAMP function, such as

SQL> create table test (created_date timestamp);

Table created.

SQL> insert into test values
  2  (to_timestamp('10.06.2008 12:09:43123', 'dd.mm.yyyy hh24:mi:ff6'));

1 row created.

SQL> select * from test;

CREATED_DATE
---------------------------------------------------------------------------
10.06.08 12:09:00.431230

SQL>

SQL*Plus User's Guide and Reference book. TOAD manual can probably be found at its site.

Report message to a moderator

Re: SQL PLUS TIMESTAMP [message #393610 is a reply to message #393233]

Mon, 23 March 2009 10:20

namitanamburi
Messages: 35
Registered: March 2009

Member

Table name = EMP

Column name Data type

id number Not null
pname varchar2(10)
created_date timestamp Not null
Mod_date timestamp Not null

I tried using your ddl which is

insert into test values
(to_timestamp('10.06.2008 12:09:43123', 'dd.mm.yyyy hh24:mi:ff6'));

but it did not let me enter only timestamp column record and returned error message ---not enough values. (I guess because there are other columns which cannot be null)

Then I tried the following ddls but nothing worked. can you help me and mention where I'm going wrong.

Insert into emp values (&id,'&pname','&created_date','&mod_date');
enter values for id : 1
enter values for pname : peter
enter values for created_date : '10.06.2008 12:09:43123', 'dd.mm.yyyy hh24:mi:ff6'
enter values for id : '12.06.2008 12:09:43123', 'dd.mm.yyyy hh24:mi:ff6'

This returned error message

Bind variable '24'not declared.

So I tried removing 24 after hh, even that didnot work.

Can you reply with correct way of inserting created_date column and mod_date cpolumn along with id and pname;

Report message to a moderator

Re: SQL PLUS TIMESTAMP [message #393611 is a reply to message #393233]

Mon, 23 March 2009 10:41

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

So you tried using to_timestamp, got an error unrelated to the timestamp and so decided to stop using it?

As littlefoot said: use to_timestamp.

Then specify all necessary columns for the insert.

Suggest you read the documentation for insert:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#SQLRF01604

Report message to a moderator

Previous Topic:	[TOAD 9.1] How to "set define off"
Next Topic:	set feedback off

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 11:30:01 CST 2024