Akhil Madivada

Akhil Madivada

  • 2.1k
  • 58
  • 6.1k

Expression in SSIS - Derived column to change the Date

Jun 9 2017 12:16 PM
I am working on SSIS and my question is I am having an XML file data in that data one column header is TIMESTAMP and the TIMESTAMP sample data is below:
 

So, How can I write an Expression in the Derived Column to change the Date from last six days? Without Changing the 'TimeReading' that is side to the Date.

That means the Date should change and pop up from previous 6 days and TimeReading next to the Date should be same as previous data.

What I did:

In the Expression, if I give GETDATE() output is populating with today's date and the TimeReading is populating with 00:00:00.

If I give GETDATE()-1, output is populating with the previous date and TimeReading is populating with 00:00:00,

But I need to get the continuous date from the last six days with the same TimeReading.

Can anyone help?

 
  1.     2013-08-02 00:15:00  
  2.     2013-08-03 00:30:00  
  3.     2013-08-04 00:45:00  
  4.     2013-08-05 08:45:00  
  5.     2013-08-06 08:45:01  
  6.     2013-08-08 08:45:02  
  7.   
  8. In the above data, I have to change the Date to:  
  9.   
  10.     2017-06-02 00:15:00  
  11.     2017-06-03 00:30:00  
  12.     2017-06-04 00:45:00  
  13.     2017-06-05 08:45:00  
  14.     2017-06-06 08:45:01  
  15.     2017-06-08 08:45:02