CODEWITHSUNDEEP
sqldatabaseoracle-databasetimestamp
user471011
user471011

Reputation: 7374

How can I update column with type TIMESTAMP in Oracle via sql query?

I have column:

"LASTTOUCH" TIMESTAMP(9) NOT NULL ENABLE

and I must set current date to this column.

But I have no idea how I can do it.

Could you help me, please?

Upvotes: 9

Views: 79275

Answers (3)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

If you want the current time (including the timestamp precision), you could use either systimestamp or current_timestamp

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
04-OCT-12 11.39.37.670428 AM -04:00

SQL> select CURRENT_TIMESTAMP from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
04-OCT-12 11.39.51.021937 AM -04:00

update table_name set column_name = SYSTIMESTAMP where id = 100;

If you just set the value to sysdate, the fractional seconds part of the timestamp is zeroed out as the date is implicitly converted to timestamp.

SQL> create table t1(
  2     time1 timestamp
  3  );

Table created.

SQL> insert into t1 values (sysdate);

1 row created.

SQL> commit;

SQL> select to_char(time1,'MM/DD/YYYY HH24:MI:SS.FF6') result from t1;

RESULT
-----------------------------
10/04/2012 11:43:07.000000

Upvotes: 6

Luke Liu
Luke Liu

Reputation: 298

INSERT INTO tableName VALUES (SYSDATE);

OR

UPDATE tableName SET COLUMN = SYSDATE;

Upvotes: 1

Koerr
Koerr

Reputation: 15723

Insert:

insert into tablename (LASTTOUCH) values (CURRENT_TIMESTAMP);

Update:

update tablename set LASTTOUCH=CURRENT_TIMESTAMP;

Upvotes: 14

Related Questions