Re: How to write date transform script

For future reference, here's how to do it. It turns out to be quite easy and
even locale safe. The trick is to use DateSerial and feed it the appropriate
data. It will take care of creating a datetime that will work with the
locale.

Function Main()
DTSDestination("Ticker") = DTSSource("Col001")
str = DTSSource("Col002")
iYear = CInt( Mid( str, 1, 4) )
iMo = CInt( Mid( str, 5, 2) )
iDay = CInt( Mid( str, 7, 2) )
DTSDestination("Date") = DateSerial( iYear, iMo, iDay)
DTSDestination("POpen") = DTSSource("Col003")
DTSDestination("PLow") = DTSSource("Col004")
DTSDestination("PHigh") = DTSSource("Col005")
DTSDestination("PClose") = DTSSource("Col006")
DTSDestination("Vol") = DTSSource("Col007")
Main = DTSTransformStat_OK
End Function

--
Richard Lewis Haggard

"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:OSKGYuJ9FHA.476@xxxxxxxxxxxxxxxxxxxxxxx
>I have stock data files to import into a SQL table. Here's a line from one
>of the files:
>
> QQQ 19990311 51.43750 51.71850 50.31250 51.31250 90108.00000
>
> Each column is separated by a tab and the import wizard has no trouble
> understanding the data but, of course, it wants to import everything as a
> varchar. It would be nice to be able to transform the data at import time
> into something more appropriate. For example, the second column is
> YYYYMMDD and the following columns are floating point numbers. What should
> the syntax be to convert the YYYYMMDD to a date?
>
> The default script that the import wizard produces for the Column Matches
> and Transforms page is is
>
> Function Main()
> DTSDestination("Ticker") = DTSSource("Col001")
> DTSDestination("Date") = DTSSource("Col002")
> DTSDestination("POpen") = DTSSource("Col003")
> DTSDestination("PLow") = DTSSource("Col004")
> DTSDestination("PHigh") = DTSSource("Col005")
> DTSDestination("PClose") = DTSSource("Col006")
> DTSDestination("Vol") = DTSSource("Col007")
> Main = DTSTransformStat_OK
> End Function
>
> The table that is to receive the data is defined as
> Ticker char 4
> Date datetime
> POpen float
> PLow float
> PHigh float
> PClose float
> Vol float
>
> so there are some obvious problems with the import. I'd like to
>
> convert DTSSource("Col002") to a datetime
> convert the remaining columns to a float.
>
> What is the syntax needed to do this?
> --
> Richard Lewis Haggard
>


.

Relevant Pages

  • How to write date transform script
    ... I have stock data files to import into a SQL table. ... the second column is YYYYMMDD ... POpen float ... convert DTSSourceto a datetime ...
    (microsoft.public.sqlserver.clients)
  • Re: Audit table, finding first change for each item
    ... The cast on aud_dt and aud_tm are because both are datetime, ... This query gives me the desired results but I just want the first change ... cast((cast(b.aud_dt as float) + cast) as datetime) ... Earlier versions of SQL Server: ...
    (comp.databases.ms-sqlserver)
  • Re: UDF and SQL2000 - Why doesnt this work?
    ... assignment to the @DailyVal variable is the problem. ... loop actually works when I return a datetime and only update the date. ... DECLARE @TempDate datetime ... DECLARE @TempVal float ...
    (microsoft.public.sqlserver.programming)
  • Re: Wrapping T-SQL in Function and it gets very slow.
    ... I have a SELECT statement that manipulate a datetime: ... GROUP BY CAST(ROUND(CAST(DischargeEventTime AS float), 0, 1) AS datetime) ... AS TimeValue FROM tblItemData WHERE DischargeEventTime between ... Why does "wrapping" SQL in a function cost so much?!? ...
    (comp.databases.ms-sqlserver)
  • UDF and SQL2000 - Why doesnt this work?
    ... The loop only seems to run through once and then it exits returning either ... datetime, @PeriodEnd datetime) ... DECLARE @TempDate datetime ... DECLARE @TempVal float ...
    (microsoft.public.sqlserver.programming)