I am importing a text file with a date field in a non-standard date format:
In other words, it's in YYYYMMDD, without quotes. I've been unsuccessful in
using DTS transformation using the dateserial function. The dateserial
function recognizes the YYYY portion as a string; however, this function
requires it to be in an integer format. Using Cint, Cast, Convert have all
been unsuccessful.

Ex. DTSdestination("Billing_Date") = dateserial(a, b, c)

a = left(DTSsource("001"), 4)
b = substr(DTSsource("001"), 5, 2)
c = right(DTSsource("001"), 2)

Any suggestions?

Thanks.
--
Craig

Does this help at all.


Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)


Allan



"Craig" <Craig (AT) discussions (DOT) microsoft.com> wrote

No...as I mentioned...Cint does NOT work.

--
Craig


"Allan Mitchell" wrote:

Given a text file that looks like this

20050605


And a destination of

CREATE TABLE A(col1 datetime)

This works for me

' Copy each source column to the destination column
Function Main()

dim a,b,c

a = left(DTSsource("Col001"), 4)
b = MID(DTSsource("Col001"), 5, 2)
c = right(DTSsource("Col001"), 2)


DTSDestination("col1") = DateSerial( a ,b,c )

Main = DTSTransformStat_OK
End Function



"Craig" <Craig (AT) discussions (DOT) microsoft.com> wrote

Hi folks,

"Allan Mitchell" wrote:
maybee I'm crazy, but when importing from a text file, why not simply use
the datetime transform?
That works for me all the time and is so easy to configure ;-)

Helge

What do you mean by datetime transform...SQL will only recognize this format
as a datetime if it has single quotes. Thanks dr

"Helge C. Rutz" wrote:

Actually, Allan's suggestion works fine. The error was on my side. I went
in and manipulated the original text file and inserted single quotes around
the date field that I was trying to import (The SQL server help suggested
that SQL "only recognizes date and time data enclosed in single quotations.")
When I removed these single quotes, Allan's suggestion worked.

Thanks for all the help and sorry for the confusion.
--
Craig


"dr" wrote:

Hi dr,

when you import from a text file with a transform data task you can select
a datetime string transform rule for that column where you specify the
expected string format.
With this transform it is regardless how the string is formated and if it
hast quotes or not.

Helge

"dr" wrote: