Home » SQL & PL/SQL » Client Tools » SQL PLUS TIMESTAMP
icon11.gif  SQL PLUS TIMESTAMP [message #393233] Fri, 20 March 2009 15:31 Go to next message
namitanamburi
Messages: 32
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.
Re: SQL PLUS TIMESTAMP [message #393244 is a reply to message #393233] Fri, 20 March 2009 16:03 Go to previous messageGo to next message
Littlefoot
Messages: 20895
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.
Re: SQL PLUS TIMESTAMP [message #393610 is a reply to message #393233] Mon, 23 March 2009 10:20 Go to previous messageGo to next message
namitanamburi
Messages: 32
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;








Re: SQL PLUS TIMESTAMP [message #393611 is a reply to message #393233] Mon, 23 March 2009 10:41 Go to previous message
cookiemonster
Messages: 12404
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
Previous Topic: [TOAD 9.1] How to "set define off"
Next Topic: set feedback off
Goto Forum:
  


Current Time: Tue Dec 06 00:26:13 CST 2016

Total time taken to generate the page: 0.10942 seconds