| Author |
Topic |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2007-12-17 : 09:26:41
|
| Hi am trying to import data from a excel file into my 2005 DB using a SSIS package. This first thing i've done is create a Excel source and then a derived column task as i need to format my date, so am using substring to format the date but the expression am using will not work am geting a error on itthe data in the excel file is like 8122007here is my expression substring(date,1,1) +"/"+ substring(date,2,2) +"/"+ substring(date,4,7)Any idea i think it's something got to do with the data type |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 09:33:42
|
| Are you populating this to a datetime field? Then i guess you need to further cast this to datetime type. |
 |
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-17 : 09:36:45
|
| At the moment i've it set to a varchar but i think your right i think i need to type cast it first before i can use the sub string on it... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 09:59:38
|
| [url]http://msdn2.microsoft.com/en-us/library/ms141005.aspx[/url]Refer to this for more idea on datetime datatypes in SSIS |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-17 : 10:11:51
|
| Hey i found away around it, or a way of doing it. This involves doing it like this, first i derived a new columns called new data and type cast it as DT_I8 with the data type as a string,Then i created another new derived task and did my substring to give me the date format 8-12-2007 again with datatype as a string,I then created a data conversion and gave the new_date column the data type of DT_Date as the date column in the database is of datetime this works fine now, it maybe the long way around it though.. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-17 : 10:12:30
|
Pad a leading zero for security.STUFF(STUFF(RIGHT('0' + Date, 8), 5, 0, '/'), 3, 0, '/')
E 12°55'05.25"N 56°04'39.16" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 10:18:09
|
| Did you try directly casting to DT_DATE from your initial substring step? |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-12-17 : 10:46:49
|
| No i did not try this how would it work?, could i then have my data type as DT_Date or would i use the data conversion to convert it |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-12-17 : 11:08:31
|
| I was asking whether you tried this?(DT_DATE)(substring(date,1,1) +"/"+ substring(date,2,2) +"/"+ substring(date,4,7)) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-17 : 14:51:32
|
And again, what if the day is greater than 9?What if the day is the 20th?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Importing from Excel using SSIS