Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
savior faire
Posting Yak Master
194 Posts |
 Posted - 2009-08-10 : 17:04:02
|
| Hello everyone,I have a question about taking data from a flat file data source that has a couple of dates that are string fields and in the format:ccyymmdd(ie: 20090810).The data in the incoming flat file is ending up populating a SQL Server 2005 database table and the two date fields will be type: datetime.The ssis data flow aborts with a conversion error when performing a DataConversion transformation. The documentation is very clear that this is a valid(legal) conversion(Cast).Any ideas?Talk sense to a fool and he calls you foolish. |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-08-10 : 17:33:03
|
| How are you converting them to datetime? |
 |
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2009-08-10 : 19:08:48
|
| In a DataConversion data flow transformation, I have tried several date data types in the data type dropdown column(dt_db_date, dt_db_timestamp, dt_date).Thanks for reading my question...Talk sense to a fool and he calls you foolish. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-08-11 : 10:10:15
|
| I'm not sure if this is right...but try using substring operator in the Data COnversion task to make the string like '2009-08-10'I would think it will be something like Substring(file_date,1,4) + '-' + substring(file_date,5,2) + '-' + substring(file_date,7,2) |
|
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2009-08-11 : 21:50:05
|
| Yeah... I will try that one... I believe I have seen the date convert work on a date string in the format ccyy-mm-dd, or mm-dd-ccyy...Thanks for the reply.Talk sense to a fool and he calls you foolish. |
|
|
|
savior faire
Posting Yak Master
194 Posts |
Posted - 2009-08-22 : 13:18:17
|
| Vijay,your suggested approach, works...Talk sense to a fool and he calls you foolish. |
|
|
|
|
|
|
|
|
Converting string input to a date type field