4

I have col1 in myTable which is varchar, and I have to insert here timestamp eg:- 09-MAY-11 10.23.12.0000 AM.

Now please tell me:

  • How to insert into myTable with taking sysdate in above format...
  • How to retrieve data from col1 in tha same timestamp format..
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Avi
  • 1,115
  • 8
  • 20
  • 30

1 Answers1

8

INSERT:

insert into myTable (col1) VALUES (to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4 AM') );

SELECT:

select to_timestamp(col1, 'dd-mon-yyyy hh.mi.ss.ff4 AM')  from myTable ;

But it is much better to store the data directly as a timestamp. Then you can compare the values ​​or modify them directly.

create table myTable1( col1 timestamp default systimestamp);
schurik
  • 7,798
  • 2
  • 23
  • 29
  • select to_timestamp(sysdate, 'dd-mon-yyyy hh.mi.ss.ff4 AM') from dual This give me '11-MAY-11 12.00.00 AM' but i want '11-MAY-11 01.45.00 PM' (i mean time is getting truncated to 12:00:00 but i want actual time) – Avi May 11 '11 at 08:06
  • `select to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4 AM') from dual;` And if you don't want and if you don't want the fractional seconds then: `select to_char(sysdate, 'dd-mon-yyyy hh.mi.ss AM') from dual;` – schurik May 11 '11 at 08:18