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
Subtract one day from a timestamp column
Moderators: chulett, rschirm, roy
Subtract one day from a timestamp column
thanks and regards,
Tanya!!!
Tanya!!!
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 13
- Joined: Fri Jul 15, 2005 12:01 am
Re: Subtract one day from a timestamp column
convert into julian date and minus number of days you want and reconvert into date format.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Subtract one day from a timestamp column
How do you handle the time component in this "solution"?satyanarayana wrote:convert into julian date and minus number of days you want and reconvert into date format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.