dataype conversion from string to date

• mike.clayton007

  Old Hand

  Points: 302

  More actions

  September 30, 2008 at 12:46 pm

  #127819

  Hi

  I am loading data from CSV files to SQL database. I have a column MFD_DATE where the values are in the form of 03SEP2008. I want to convert this into date datatype. I tried using data conversion transformation but its throwing a error. It seems like I need to use Derived column transformation, I tried with few expressions but unable to do it.

  Thanks

• pduplessis-723389

  SSChampion

  Points: 10599

  More actions

  October 1, 2008 at 9:43 am

  #879581

  Or perhaps have a date dimension with this kind of date and a date equivalent.

  Thereafter you could simply perform a lookup task and reference the proper date?

• Prachi-885067

  SSC Veteran

  Points: 218

  More actions

  October 3, 2008 at 2:00 am

  #880334

  Use the below transformation in derived column and then use data conversion to convert to Date data type

  SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],3,3) + "/" + SUBSTRING([Column 0],6,4)

  where Column 0 is date column name from .csv file and it is of format 03SEP2008.

  Or you can use directly use below transformation to get date.

  (DT_DBDATE)(SUBSTRING([Column 0],1,2) + "/" + SUBSTRING([Column 0],3,3) + "/" + SUBSTRING([Column 0],6,4))

  I could not convert string 03SEP2008 to Date using data conversion.