update a column in sql using ssis derived column datatype conversino error

• jeevan.jeev1

  SSC Enthusiast

  Points: 122

  More actions

  May 16, 2011 at 7:35 am

  #238221

  Hi,

  I have a table with three columns with the following names and data types of the columns

  column name datatype

  contract true/false

  expirationdate date

  expirationdatenotification date

  i have the data in contract and expirationdate columns, i have to update the data in expirationdatenotification with the following condition

  if the contract column value is YES and update the expirationdatenotification column value to one month minus 6 days with respect to expiration date. for example if the epirationdate is 31/12/2011, the value in the expirationdatenotification should be 25/11/2011.

  i have added a dataflow task in ssis and in that added a OLEDB SOURCE control and added a derived column. in the derived column expression i want ot write the logic can someone please help me here.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  May 16, 2011 at 8:19 am

  #1325079

  Here is some code that should let you get what you need.

  create table #test

  (

  HasContract bit,

  ExpirationDate date,

  ExpirationNotificationDate date

  )

  insert #test select 1, '12/31/2011', null

  update #test set ExpirationNotificationDate = DATEADD(d, -6, DATEADD(m, -1, ExpirationDate))

  select * from #test

  drop table #test

  Notice how I created a table and some test data so it is easy to test and evaluate?

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• jeevan.jeev1

  SSC Enthusiast

  Points: 122

  More actions

  May 16, 2011 at 11:59 pm

  #1325600

  Hi,

  Thanks a lot it fixed my issue.