Convert DATETIME to TIMESTAMP

• rahulsony111

  Hall of Fame

  Points: 3865

  More actions

  December 27, 2009 at 12:21 pm

  #140235

  As i said i have to convert DATETIME to TIMESTAMP.

  But i cant do it directly converting MSSQL DATETIME to ORACLE TIMESTAMP.

  So i want to create a staging table where i can convert MSSQL DATETIME to MSSQL TIMESTAMP.

  Later i will move that staging table to ORACLE.

  Please help me

• Lowell

  SSC Guru

  Points: 323522

  More actions

  December 27, 2009 at 12:40 pm

  #1096579

  ahh, SQL and oracle TIMESTAMPS are not the same thing.

  in Oracle, the TIMESTAMP data type is a very granular datetime field.

  SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t;

  Date

  -----------------------------------------------------

  20-JUN-03 04.55.14.000000 PM

  26-JUN-03 11.16.36.000000 AM

  IN SQL,

  the datatype name TIMESTAMP in SQL is very misleading..that's why it is deprecated and should be called ROWVERSION;

  note that it is NOT any sort of date/datetime type of object...it is only a unique number to represent when an update occurs. it's not related to a date in any way.

  a SQL datetime datatype is close to an oracle TIMESTAMP datatype, but with an accuracy of only +- 3 milliseconds.

  In SQL server, you cannot assign a value to a column of the datatype TIMESTAMP/ROWVERSION; it gets it's value from the server itself on insert/update.

  to convert a SQL datetime to oracle timestamp, if you are using a linked server, the driver will handle it automaticaly:

  INSERT INTO MyOracleLinkedServer..SomeSchema.SomeTable (... , timestamp_col)

  VALUES (...., '2007-10-01T01:02:03.004');

  otherwise, you could format the SQL date to a char, and use the TO_TIMESTAMP function in oracle to convert it explicitly(it's just like TODATE or TO_NUMBER)

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!