Monthly Oracle data import into SQL Server 2000 1

[nimarii]

I'm completely new to SQL Server 2000, and I have a project to create and deploy a client server system with SQL Server and C#.NET.

The data comes from monthly .DAT files, which actually originate from someone else's Oracle database.

I'm just trying to wrap my head around the process of how I'm going to get everything working, and the first problem I'm having is converting the Oracle timestamp field into the SQL Server datetime field.

When I asked the DBA of the Oracle database about the timestamp field, this was his reply:

It is a time stamp field ( similar to the time_t values in C ).
Basically - in Oracle - you can use "TO_DATE( '19700101', 'YYYYMMDD' ) + BEG_STAMP / 86400" to translate a time stamp to a date /time.

he said that it was possible to convert this into SQL Server datetime, but he didn't say how.

Does anyone know how to do this? Also, any suggesstions about general tips when importing monthly data would be extremely helpful...

many thanks!

 

[mrdenny]

I've written a FAQ that covers this. faq183-5563

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[nimarii]

wow! thats so simple!

so i take it that those numbers and dates never change? also, is the divider supposed to be 86100 or 86400?

and sorry for the primitive question, but what does the "dd" represent?

 

[nimarii]

ok - i figured it out. great FAQ!

 

[mrdenny]

Thanks. Yeah, the date 1/1/1970 is static. It's a unix thing which is where oracle got it from.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[nimarii]

Do you know how I can derive the date and time as well?

 

[mrdenny]

This appears to be working correctly.

declare @UnixTime bigint
set @UnixTime = 1101859754

select dateadd(ss, ((convert(numeric(24,6), @UnixTime)/86400)-(@UnixTime/86400))*86400, dateadd(dd, (@UnixTime/86400), '1/1/1970'))

I'll update my FAQ with this info.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[nimarii]

mrdenny - this worked like a charm - thanks so much!

 

[nimarii]

mrdenny - i apologize for the million questions!

with your code, i was able to translate the oracle timestamp field into a readable date/time, but now I need to store that data into SQL Server, and I'm not sure that after converting it, it will store properly in a data field of type "datetime"?

is there another type of conversion that will allow me to store the Oracle timestamp data type in SQL Server?

sorry if i'm running around in circles, i'm new at this, and the datetime field type is throwing me for a loop...

 

[mrdenny]

The data as it comes out of that should go into a datetime field no problem. If needed you can write it in a convert(datetime, ...) like this.

declare @UnixTime bigint
set @UnixTime = 1101859754

select [COLOR=blue]convert(datetime,[/color] dateadd(ss, ((convert(numeric(24,6), @UnixTime)/86400)-(@UnixTime/86400))*86400, dateadd(dd, (@UnixTime/86400), '1/1/1970'))[COLOR=blue])[/color]

No problem. If I didn't want the questions, I wouldn't post responses.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)