CODEWITHSUNDEEP
sqloracle-database
user1472409
user1472409

Reputation: 407

Insert Timestamp into Varchar2 column in Oracle Database

I am working with an application that interfaces with an Oracle 11g database. I need to insert a timestamp in the YYYYMMDDhhmmss format, into a Varchar2 column.

My SQL update query is:

update CODELIST_XREF_ITEM set RECEIVER_ITEM=TIMESTAMP where LIST_NAME='BUSINESS_PROCESS';

The TIMESTAMP variable is where it need to be inserted. I have tried setting the TIMESTAMP variable to CURRENT_TIMESTAMP, however I am not able to format that.

Any suggestions?

Thanks in advance for all your help!

Upvotes: 0

Views: 3534

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

If you want to insert the character representation of a timestamp (it actually appears that you want the character representation of an Oracle date since you don't want fractional seconds or a time zone), you'd use the to_char function.

to_char( sysdate, 'YYYYMMDDHH24MISS' )

So your UPDATE statement would be

update CODELIST_XREF_ITEM 
   set RECEIVER_ITEM=to_char( sysdate, 'YYYYMMDDHH24MISS' )
 where LIST_NAME='BUSINESS_PROCESS';

If you wanted to store fractional seconds or a time zone or do something else that required a timestamp, you'd use the same to_char function just with a different format mask.

Upvotes: 3

Related Questions