Need help converting DT_STR to datetime using SSIS

Question

Need help converting DT_STR to datetime using SSIS

Pat_B

SSCommitted

Points: 1526
More actions
January 29, 2010 at 1:53 pm

#140922

I have an ANSI text flatfile that I am importing the date/time from and ultimately want to import it into a sql column that has a data type of datetime.
So, the external OpenDate column is importing in as DT_STR with 10 chars max. I planned on deriving a column based on this to convert it to datetime, but I'm stumped on how to do this.
The date/time from the flatfile can be in either of these formats:
1/1/1998 12:00:00 AM
10/12/2009 12:00:00 AM
If the date/time was only in mm/dd/yyyy hh:mm:ss format this would be fairly straightforward to extract using substrings, but since the date/time varies and could be also be in m/d/yyyy hh:mm:ss format, this poses much more of a challenge.
Also, if i were to use a type cast such as (DT_DBTIMESTAMP) [OpenDate] I believe it would leave the date/time column in this format yyyy-mm-dd hh:mm:ss:ffffffff which is not desired either.
Any help is greatly appreciated.
Thanks much!
Pat B.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Pat_B SSCommitted Points: 1526 More actions · Answer 1

This is what I came up so far, but I don't know how to combine everything into one line that I could use in a derived column.

slash1 and slash2 are integer variables that represent the occurrences of the / symbol in the date

slash1 = charindex('/', [OpenDate])

slash2 = charindex('/', [OpenDate], slash1)

month = substring([OpenDate], 1, slash1 - 1)

day = substring([OpenDate], slash1 + 1, slash1 + slash2 - 1)

YearAndTime = substring([OpenDate], slash1 + slash2 + 1, 16)

Combined would look something like this, but I'm not certain.

(DT_DBTIMESTAMP)(month + "/" + day + "/" + YearAndTime)

I believe that I'm close, but how could I combine all into a nice long chain of commands without having to define the slash1 and slash2 variables?

Thanks.

Pat B.

Pat_B SSCommitted Points: 1526 More actions · Answer 2

Here I tried to substitute for the variables (slash1, slash2, month, day, YearAndTime) and came up with this crazy long chain of commands, but I'm getting "cannot parse expression" errors.

(DT_DBTIMESTAMP)(substring([OpenDate], 1, charindex('/', [OpenDate]) - 1) + "/" + substring([OpenDate], charindex('/', [OpenDate]) + 1, charindex('/', [OpenDate]) + charindex('/', [OpenDate], charindex('/', [OpenDate])) - 1) + "/" + substring([OpenDate], charindex('/', [OpenDate]) + charindex('/', [OpenDate], charindex('/', [OpenDate])) + 1, 16))

Pat

Pat_B SSCommitted Points: 1526 More actions · Answer 3

I broke this down/simplified it to isolate the source of the issue. The substring function works by itself, but i'm getting "function name not recognized or does not exist" when trying to get the charindex function to work.

substring([OpenDate], 1, charindex("/", [OpenDate]))

Phil Parkin SSC Guru Points: 246965 More actions · Answer 4

That was fun! 🙂

Use this as your derived column definition:

SUBSTRING([OpenDate],FINDSTRING([OpenDate],"/",2) + 1,4) + "-" + SUBSTRING([OpenDate],1,FINDSTRING([OpenDate],"/",1) - 1) + "-" + SUBSTRING([OpenDate],FINDSTRING([OpenDate],"/",1) + 1,FINDSTRING([OpenDate],"/",2) - FINDSTRING([OpenDate],"/",1) - 1) + SUBSTRING([OpenDate],LEN([OpenDate]) - 11,12)

and then feed that through a data conversion transform to cast the output to DB-DATE.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Pat_B SSCommitted Points: 1526 More actions · Answer 5

This is what worked for me as a derived column definition on the DT_WSTR input column:

[OpenDate] == "#EMPTY" ? NULL(DT_WSTR,255) : SUBSTRING([Open Date_2],FINDSTRING([Open Date_2],"/",2) + 1,4) + "/" + SUBSTRING([Open Date_2],1,FINDSTRING([Open Date_2],"/",1) - 1) + "/" + SUBSTRING([Open Date_2],FINDSTRING([Open Date_2],"/",1) + 1,FINDSTRING([Open Date_2],"/",2) - FINDSTRING([Open Date_2],"/",1) - 1) + " " + SUBSTRING([Open Date_2],FINDSTRING([Open Date_2]," ",1) + 1,11)

After this, I used a data conversion to convert DT_WSTR to DT_DBTIMESTAMP.

Thanks for all the help!

Pat B.