Help - importing smalldatetime values from text in ActiveX script -- sql server dts

bill_sheets NO[at]SPAM hotmail.com
6/30/2005 9:08:21 AM

I have an input file with date & time data in this
format:

yy mm dd hhmm..........
99 12 7 1400

In an ActiveX script, I convert this to be mm/dd/yy hh:mm,
and have tried using CDate() and FormatDateTime(), and when
I use a MsgBox to display the value, it displays similar to
'12/7/1999 2:00:00 PM'

So far, everything I have tried to import data into a smalldatetime
column has failed.

I would rather not code a separate Transact-SQL DTS task,
but I can do that if there is no way to do this in VBScript.

I would really like to hear any suggestions anyone has.

tia,
Bill

Allan Mitchell
6/30/2005 8:43:40 PM

What about something like

dim year,month,day,hour

year = left(DTSSource("Col001"),2)
Month = Mid(DTSSource("Col001"),4,2)
Day = Mid(DTSSource("Col001"),8,1)

hour = Mid(DTSSource("Col001"),10,2)

DTSDestination("Col001") = DateAdd("H",Cint(hour),DateSerial( year ,month
,day ))

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

[quoted text, click to view]

sql server dts : Help - importing smalldatetime values from text in ActiveX script