DSXchange

Hi

I have a table with two columns Employee_id as Varchar(14) and Emp_Joining_Date as timestamp

Employee_id Emp_Joining_Date
abc 01/04/2004 00:00:00
efg 30/06/2006 00:00:00

I need to design a job to update the same table with the Emp_Joining_Date as one day lesser than the original date.So my output should be

Employee_id Original_Emp_Joining_Date Corrected_Emp_Joining_Date
abc 01/04/2004 00:00:00 31/03/2004 00:00:00
efg 30/06/2006 00:00:00 29/06/2006 00:00:00

My job is like ODBC stage-->transformer--->ODBC stage

Could anyone please let me know how/what I should pass in the transformer as the derivation to get the Corrected_Emp_Joining_Date from Original_Emp_Joining_Date?

Is there any pre defined function like field that can do this?

If I try using the dateadd function in the sql that I define in the source ODBC stage
like the one below,it is not accepted.

select Employee_id,Original_Emp_Joining_Date,DATEADD(DD, -1,Original_Emp_Joining_Date) as Corrected_Emp_Joining_Date from source_table

It would be a great help if some one breaks out a solution.

Thanks in advance,

Tanya

what about using TimestampFromSecondsSince(%seconds%,[%timestamp%]) with -86400 as seconds.

In case you don't have the time part in source you can use DateFromDaysSince().

convert into julian date and minus number of days you want and reconvert into date format.

satyanarayana wrote:convert into julian date and minus number of days you want and reconvert into date format.

How do you handle the time component in this "solution"?