How do I use DTS to convert a Date from float in YYYYMMDD 1

[jpittawa]

The source is LeasePlus, which is BTrieve. The date fields are floats in YYYYMMDD format, e.g.19990601. I need to use DTS to put some LeasePlus tables into SQL Server 7.0 for reporting. I would like to convert the dates into the Microsoft DateTime format in a DTS package that runs nightly. How do I do that?

 

[quatermass]

Hi,

To do this, you will need to do an ActiveX transform with the following script added in below the main section of the code...


Function xformdate (pass_date)
dim temp_date 'date to be passed back
dim d 'day
dim m 'month
dim y 'year


'set the day, month and year sections of the date and put together the final date
d = mid(pass_date, 7, 2)
m = mid(pass_date, 5, 2)
y = mid(pass_date, 1, 4)

temp_date = y & "/" & m & "/" & d

'check that the date being passed back is a real date and that the original date field was not 0
if IsDate(temp_date) = false then temp_date = ""
if pass_date = "0" then temp_date = ""

'pass the corrected date back
xformdate = temp_date
end function



To use the function, call it from the main section of the code...

DTSDestination("sql datatime field&quot = xformdate(DTSSource("BTrieve numeric field&quot)




I have found this bit of code very useful in preparing datetime fields. The check and default value may need to be modified, depending on your particular situation.


Hope it helps.

Kevin
**************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[jpittawa]

quatermass (Kevin), thanks. I'll give this a try.