Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert DATETIME to TIMESTAMP Expand / Collapse
Author
Message
Posted Sunday, December 27, 2009 12:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 12,905, Visits: 32,161
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #839284
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse