Hi,

I'm pulling data from an access table and the column that
i'm pulling from is a date/time column, but I want to
represent it as numerical. The problem is, the numbers in
the field represent time intervals. This is an example of
the data.."12:01:06 AM" what that really means is 1
minute and 6 seconds. I can't change the data type in
access table, but I want it sql server to look at it as
numerical. I've parsed it, but still run into problems.
Does anyone have any suggestions?

So you want to extract from your example

01 into minutes and 06 into seconds ?

What problems did you run into?

for the example given this would work

dim str
dim arr

str = "12:06:01"
arr = SPLIT(str,":")

msgbox Cint(arr(1))
msgbox Cint(arr(2))



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"borr" <bdorr39 (AT) hotmail (DOT) com> wrote

Hi Alan,

So I tried using this but it didn't work. How would you
use that example with fields?

Function Main()
dim str
dim arr

str =DTSSource("CallLength")
arr = SPLIT(str,":")
DTSDestination("Duration")=Cint(arr(1))&Cint(arr(2 ))


Main = DTSTransformStat_OK
End Function

the destination of Duration must be a character column yes ?


arr = SPLIT(DTSSource("CallLength"),":")
DTSDestination("Duration")= Cstr(arr(1)) & " minutes AND " & CStr(arr(2)) &
" seconds."



--
--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"borr" <bdorr39 (AT) hotmail (DOT) com> wrote

Thanks Alan...You're the man!