problem with conversion of a char data type to a datetime data type
-
Hi, I am working with an SSIS package(2005), which dumps records from a csv file into TABLE1. After making some alterations to thses records, it is gain inserted into another TABLE2 in the DB. One of the columns is a date column. TABLE2 has all the datatypes fixed and it has a col with datatype "smalldatetime". TABLE1 is created each time the package is run. So at first i made the TABLE1.Rdate col datatype datetime but when i try converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range datetime value." I tried changing the date col datatype to VARCHAR but again at the time of converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". The date in the TABLE1.Rdate col is in the format '2009-10-13'. Can anyone help me with this Please? Thanks & regards, Payal
-
Hi, I am working with an SSIS package(2005), which dumps records from a csv file into TABLE1. After making some alterations to thses records, it is gain inserted into another TABLE2 in the DB. One of the columns is a date column. TABLE2 has all the datatypes fixed and it has a col with datatype "smalldatetime". TABLE1 is created each time the package is run. So at first i made the TABLE1.Rdate col datatype datetime but when i try converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range datetime value." I tried changing the date col datatype to VARCHAR but again at the time of converting this col to smalldatetime for inserting into TABLE2 it gives an error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". The date in the TABLE1.Rdate col is in the format '2009-10-13'. Can anyone help me with this Please? Thanks & regards, Payal
I always just chuck everything into a varchar field when using SSIS and let the down stream stored proc do all the transforms. I can deal with problems better in a stored proc than insinde an SSIS package. Look in BOL for CONVERT and use one of the modifiers, 111 - Japan looks like the most likely
CONVERT(DATETIME, varcharfield,111)
Never underestimate the power of human stupidity RAH
-
I always just chuck everything into a varchar field when using SSIS and let the down stream stored proc do all the transforms. I can deal with problems better in a stored proc than insinde an SSIS package. Look in BOL for CONVERT and use one of the modifiers, 111 - Japan looks like the most likely
CONVERT(DATETIME, varcharfield,111)
Never underestimate the power of human stupidity RAH