Century date conversions

Hi,
I'm trying to load date fields into SQLServer using DTS, but the
format of the raw data is the number of days since 1 Jan 1900. How do
I convert this to a useful format, is there a standard conversion
routine?

Thanks
Timtim.philbrook@.cazenove.com (Helsop) wrote in message news:<1609a822.0408180233.9fbed33@.posting.google.com>...
> Hi,
> I'm trying to load date fields into SQLServer using DTS, but the
> format of the raw data is the number of days since 1 Jan 1900. How do
> I convert this to a useful format, is there a standard conversion
> routine?
> Thanks
> Tim

There are (at least) two possible solutions. First, load the data into
a staging table and clean it up with TSQL, before an INSERT into the
final table:

insert into dbo.Destination
(col1, datetime_column, ...)
select col1, dateadd(dd, numdays_column, '19000101'), ...
from dbo.Staging

Alternatively, if you want to do the transformation in DTS, then you
could use the VBScript DateAdd() function in an ActiveX column
transformation to achieve the same thing.

Simon|||--> use DATEADD(dd,@.OffsetDays,'1 Jan 1900')

DECLARE @.OffsetDays BIGINT
SET @.OffSetDays = 38217
select DATEADD(dd,@.OffsetDays,'1 Jan 1900')

Tx