Post reply

Convert DATETIME to TIMESTAMP

  • 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

  • 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!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply