Subtract one day from a timestamp column

[Tanya]

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

[priyadarshikunal]

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().

[satyanarayana]

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

[ray.wurlod]

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"?