Thursday, January 29, 2009

Oracle Date Numbers to SQL Server Datetime

File this under ‘blogging so I never have to recall this from memory’.  One of my current clients is extracting data from an Oracle database to be loaded into a SQL Server Analysis Services (SSAS) cube.  Of course, the date columns contain floats, representing the number of days since January 1, 1900 in the integer portion and the fraction is the portion of the date (time of day) for this datetime.

Fortunately, we are only concerned with the date grain of data, so I am able to use dateadd(day, <<column with oracle>>,’1/1/1900’) to calculate a SQL Server date time (this is a 2005 project).

1 comment:

atlas245 said...

I thought the post made some good points on extracting data, For extracting data i use python for simple things, data extraction can be a time consuming process but for other projects that include files, the web, or documents i tried "extracting data from the web" which worked great, they build quick custom screen scrapers, extracting data, and data parsing programs