Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Convert DATETIME to TIMESTAMP Expand / Collapse
Posted Sunday, December 27, 2009 12:21 PM


Group: General Forum Members
Last Login: Sunday, September 26, 2010 8:29 AM
Points: 113, Visits: 265
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
Post #839282
Posted Sunday, December 27, 2009 12:40 PM



Group: General Forum Members
Last Login: Today @ 7:26 AM
Points: 14,545, Visits: 38,406
ahh, SQL and oracle TIMESTAMPS are not the same thing.
in Oracle, the TIMESTAMP data type is a very granular datetime field.
20-JUN-03 PM
26-JUN-03 AM

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 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)


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!
Post #839284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse