How Do I in the formula for the Derived Column Transformation Subtract 1 from the Day

Question

 

Version and SP of SQL Server I am using
--SQL Server 2005, SP3

This is how the data looks in the csv file:
Ticker      LAST_UPDATE      LAST_UPDATE_DT
T1          19:35         10/5/2012
T2          10/5/2012         10/5/2012   <---NOTE that the time here is incorrect and I correct it in the Derived Column 1 equation

Below I concatenate the date as 10/5/2012 19:35

 

In my SSIS package I have a Derived Column Transformation that has the following:

Derived Column 1        FINDSTRING((DT_WSTR,1252)(LAST_UPDATE),"/",1) > 0 ? "00:00:00" : LAST_UPDATE

Derived Column 2        RIGHT("0" + (DT_WSTR,2)MONTH((DT_DATE)(LAST_UPDATE_DT)),2) + "/" + RIGHT("0" + (DT_WSTR,2)DAY((DT_DATE)(LAST_UPDATE_DT)),2) + "/" + RIGHT((DT_WSTR,4)YEAR((DT_DATE)(LAST_UPDATE_DT)),4) 

Then I have a second Derived Column Transformation that does this
Date_Time           [Derived Column 2] + " " + [Derived Column 1]

 

However, the data in the file is incorrect and the vendor does not want to change their program so I am being asked to do it.  Thus, how do I in the equation "RIGHT("0" + (DT_WSTR,2)MONTH((DT_DATE)(LAST_UPDATE_DT)),2) + "/" + RIGHT("0" + (DT_WSTR,2)DAY((DT_DATE)(LAST_UPDATE_DT)),2) + "/" + RIGHT((DT_WSTR,4)YEAR((DT_DATE)(LAST_UPDATE_DT)),4)"

subtract 1 day so that when I concatenate it reads as      10/4/2012 19:35

---

lcerni

Answer 1

Hi,

How about this

"RIGHT("0" + (DT_WSTR,2)MONTH((DT_DATE)(LAST_UPDATE_DT)),2) + "/" + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD(DD,-1,(DT_DATE)(LAST_UPDATE_DT))),2) + "/" + RIGHT((DT_WSTR,4)YEAR((DT_DATE)(LAST_UPDATE_DT)),4)"

---

Rajkumar