CODEWITHSUNDEEP
sqloracle-database
Wundwin Born
Wundwin Born

Reputation: 3475

Oracle example value for TIMESTAMP(0) WITH TIME ZONE datatype

Hi how will be example value for the column with datatype of TIMESTAMP?

Need some insert operation for it. createdTime is datatype of TIMESTAMP(0) WITH TIME ZONE

INSERT INTO table1 values(<id>, <firstName>, <createdTime>);

When I try like the following

INSERT INTO table1 values(1, "John", TIMESTAMP '06-APR-14 06.00.42.000000000 PM ASIA/SINGAPORE');

gives the SQL error;

SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

Upvotes: 0

Views: 1058

Answers (1)

Alex Poole
Alex Poole

Reputation: 191455

If you're using a timestamp literal you have to use a specific format:

INSERT INTO table1 values(1, 'John',
  TIMESTAMP '2014-04-06 18:00:42.0 ASIA/SINGAPORE');

You can be more flexible with a to_timestamp_tz function call:

INSERT INTO table1 values(2, 'John',
  TO_TIMESTAMP_TZ ('06-APR-14 06.00.42 PM ASIA/SINGAPORE', 
    'DD-MON-RR HH:MI:SS AM TZR'));

SQL Fiddle demo.

I've omitted the FF format element since you don't want fractional seconds.

I've also changed the double quotes around John to single quotes, to stop it being interpreted as an identifier.

Upvotes: 1

Related Questions