2 Replies Latest reply: Jun 16, 2008 8:00 AM by dba-india RSS

    Timestmap Problem

    621964
      Hi,
      I am working on migration project which include migration of SQL SERVER 2005 Database to Oracle 10g.
      As you know Getdate() function in SQL SERVER returns value in following format :- 'yyyy-mm-dd hh24:mi:ss:ff3'.
      Now i am trying to migrate this function in oracle. So i use SYSTIMESTAMP. But the problem is that its format is different. I have used TO_TIMESTAMP to insert the values in Getdate() format but it is throwing error.
      Please suggest me solution for the same as early as possible as i have to complete it before deadline.
        • 1. Re: Timestmap Problem
          3520
          Both Date and Timestamp values are stored in Oracle in internal format. You don't care what the format is, you do care how results are shown in your application.
          For example:
          SQL> create table blahh (a timestamp);

          Table created.

          SQL> insert into  blahh values (systimestamp);

          1 row created.
          To see the default format:
          SQL> select * from blahh;

          A
          -----------------------------------------------------
          07-FEB-08 04.03.59.663075 PM
          To see just the year:
          SQL> select to_char(a, 'YYYY') from blahh;

          TO_C
          ----
          2008
          Gints Plivna
          http://www.gplivna.eu
          • 2. Re: Timestmap Problem
            dba-india
            You can get the data in application in the way you want by specifying the date format.