How to store Oracle TimeStamp precision 6 in SQL Server DateTime

I've a scenario where I've to move data from a Oracle Table to a SQL Server 开发者_如何转开发table. The column in Oracle is Timestamp(6) 6 being the precision. Now I cannot send the Oracle Data in string format. I pull the data straight from the Oracle Table, so cannot format it on oracle side. What I can do is format the data using VB6.Format to make it suitable for SQL Server DateTime. I'm doing this for Oracle Date to SQL Server Datetime.

Can something similar be done for timestamp and Datetime.

That depends on the version of SQL server you're using. As @onedaywhen pointed out in his comment you could use DATETIME, but you'll lose some precision as DATETIME is only accurate to around 3 decimal digits (3.33 millisecond accuracy); however, if you're on an earlier version of SQL Server that's what you've got.

If you're running SQL Server 2008 you can use DATETIME2, which is accurate to 100 nanoseconds or 7 digits, which would handle what you're trying to do.

If you really wanted to you could hack something up to represent dates/times using DECIMAL columns. You might make the columns DECIMAL(20, 6) and format them as

YYYYMMDDHHIISS.ffffff

where

YYYY=year
MM=month (01-12)
DD=day
HH=hour (24 hour clock)
II=minutes
SS=seconds
ffffff=fractional seconds

Implementation is left as an exercise to the reader.

(CAVEAT: should you choose to use something like this, be aware that this same date/time format was used as the basis of timekeeping in the original version of the Necronomicon, and is rumored to have directly resulted in the author, Abdul Alhazred, being known as "the mad Arab". :-)

Cthulhu fthagn.