January 29, 2010 at 1:53 pm
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.
January 29, 2010 at 2:20 pm
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.
January 29, 2010 at 2:48 pm
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
January 29, 2010 at 3:05 pm
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]))
January 30, 2010 at 4:31 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 30, 2010 at 2:25 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply