SSIS: string to smalldatetime

I have a date & timestamp loading in from a text file (YYYYMMDDHHMMSS) that I need to convert to a dbtimestamp and match against a smalldatetime field in a table.

I'm loading the data in as a string and then via the Derived column tool I do a DT_DBTEIMESTAMP type cast (into the format as I see it in a sql statement YYYY-MM-DD HH:MM:00), but the process keeps erroring out at that step. Is the internal format different (MMDDYYYHHMMSS) or what am I missing?

Who is Participating?
Reza RadConnect With a Mentor Consultant, TrainerCommented:
what about this one:

(DT_DBTIMESTAMP)(SUBSTRING(IntervalStartDateTime,5,2) + "-" + SUBSTRING(IntervalStartDateTime,7,2) + "-" + SUBSTRING(IntervalStartDateTime,1,4) + " " + SUBSTRING(IntervalStartDateTime,9,2) + ":" + SUBSTRING(IntervalStartDateTime,11,2) + ":00")

try it.

wppiexpertsAuthor Commented:
As an additional note: the type cast format I'm using is:
(DT_DBTIMESTAMP)(SUBSTRING(IntervalStartDateTime,5,2) + "-" + SUBSTRING(IntervalStartDateTime,7,2) + "-" + SUBSTRING(IntervalStartDateTime,1,4) + " " + SUBSTRING(IntervalStartDateTime,9,2) + "-" + SUBSTRING(IntervalStartDateTime,11,2) + ":00")

and this fails during execution.

If I remove the portion of the datatype that represents time (SUBSTRING(IntervalStartDateTime,9,2) + "-" + SUBSTRING(IntervalStartDateTime,11,2) + ":00") then this step works fine, so am I not building the datetime value properly?
Jason Yousef, MSSr. BI  DeveloperCommented:
1-Why would you set 50 points to the question while you're a Premium Service Member and have no limit on points !

2-what's the error msg you're getting? the answer should be obvious in the error msg.

3-DT_DBTEIMESTAMP Cast will cast from "date" to DT_DBTEIMESTAMP, and you're not sending "date" type in the pipe!

4-Try a Derived COLUMN Transfer and type this to convert the YYYYMMDD to YYYY-MM-DD
in the expression you must have

Derived Column name Derived column Expression
Date  <Replace Date> SUBSTRING ([DATE] , 1 , 4) + "/" +SUBSTRING ([DATE] ,5 , 2) + "/" +SUBSTRING ([DATE] ,7 , 2)

5-then cast the above to DT_DBTEIMESTAMP

so the final query should be:

(DT_DBTIMESTAMP)(SUBSTRING ( [Stmt From]  , 1 , 4) + "/" +SUBSTRING ([Stmt From] ,5 , 2) + "/"+ SUBSTRING ([Stmt From] ,7 , 2))

hope that helps :)
wppiexpertsAuthor Commented:
thats what I'm finding a bit confusing about the conversion process. If my original value was "YYYYMMDDHHMMSS" and I build an expression in the Derived column transformation in the format "YYYY-MM-DD HH:MM:00" it fails using the DT_DBTIMESAMP, but if I remove the time reference, it works fine. I was under the assumption that if I passed the function a valid datetime in the proper format, it would change it from a string to a datetime datatype.

so, I guess my underlying questions are:
1. How do I use the derived column transformation to take a string and create a datetime value
2. Based on the expression I posted above, what am I doing wrong in that it doesn't recognize it as a datetime?

wppiexpertsAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.