Page 1 of 2
Posted: Fri Aug 17, 2012 11:46 pm
by chulett
Do the calculation in the job, not the SQL. That or talk to a SQL Server guru there to get the proper SQL syntax, whatever that may be.
Posted: Fri Aug 17, 2012 11:52 pm
by ArndW
The datatype "smalldatetime" in SQL-Server would be a DataStage datatype of "timestamp". One cannot just subtract one from the other and expect that the program knows you want the answer as an integer number of months (instead of days, or decimal fractions of a year, or minutes, or seconds).
Code: Select all
DaysSinceFromDate(TimeStampToDate(issue_dt),TimeStampToDate(expiry_dt))
Will give you the number of days between the two dates. From there you can compute the months.
Posted: Sat Aug 18, 2012 6:18 am
by bhasds
Hi hargun,
You can try the below code which will calculate the difference between the issue_dt and expiry_dt, and give you the result as an integer.
Code: Select all
Neg(MonthFromDate(issue_dt)- MonthFromDate(expiry_dt))
Posted: Sat Aug 18, 2012 7:42 am
by chulett
So... you are thinking that you can subtract the two month numbers regardless of the year? Unfortunately, it's not as simple as that.
Another game you can play, if you don't want to worry about how many days are in any given month, is to do some math on the components. Take the year and month from each date and compute the total number of months in it:
Then subtract the two results.
Posted: Sat Aug 18, 2012 8:03 am
by hargun
bhasds wrote:Code: Select all
Neg(MonthFromDate(issue_dt)- MonthFromDate(expiry_dt))
hi ,
i have tried with this conversion but while compling the job getting the error of invalid converison from timestamp to date
Posted: Sat Aug 18, 2012 8:18 am
by hargun
Hi Arnd,
Can you provide the code that i can use in transformer to get the number of days and then compute the months
Posted: Sat Aug 18, 2012 8:24 am
by chulett
He did. At least for the "days" part, the month he left up to you.
Posted: Sat Aug 18, 2012 10:10 am
by hargun
Hi chullet,
i know he did for days being very new to datastage can you please provide the whole code .....
Datatype conversions
Posted: Sat Aug 18, 2012 10:56 am
by bhasds
Hi Hargun,
The below code should work if you have timestamp in source.
Code: Select all
Neg(MonthFromDate(TimestampToDate(update.IssueDate))- MonthFromDate(TimestampToDate(update.ExpiryDate)))
Re: Datatype conversions
Posted: Sat Aug 18, 2012 11:10 am
by hargun
Thanks for providing this solution.I appreciate.This resolves.....
Datatype conversions
Posted: Sat Aug 18, 2012 11:14 am
by bhasds
Or
If you are using the ArndW's code then divide the output of ArndW's code by 30,assuming the number of days per month is 30 .
Code: Select all
Neg(DaysSinceFromDate(TimeStampToDate(update.IssueDate),TimeStampToDate(update.ExpiryDate)))/30
Re: Datatype conversions
Posted: Sat Aug 18, 2012 11:51 am
by hargun
Hi bhasd
so both
Neg(MonthFromDate(TimestampToDate(update.IssueDate))- MonthFromDate(TimestampToDate(update.ExpiryDate
or
Neg(DaysSinceFromDate(TimeStampToDate(update.IssueDate),TimeStampToDate(update.ExpiryDate)))/30
Can you please advise on this i can use either one of them giving me the same result
Posted: Sat Aug 18, 2012 12:48 pm
by bhasds
Hi Hargun,
Both the code should give you the same result.So, its your wish which one to use.
Posted: Sat Aug 18, 2012 9:14 pm
by chulett
Sorry, but you two need to test the posted code a little better if you think they both return the same result in all cases. Using the MonthFromDate() function is not the correct solution.
Posted: Sun Aug 19, 2012 3:42 am
by bhasds
Hi ArndW/Chullet,
I am sorry for misleading hargun.In the above scenario the code given by me will give erroneous result.
Can you please tell whether the below modification in your code is fine to get the difference between the two dates in form of month?
Code: Select all
Neg(DaysSinceFromDate(TimeStampToDate(update.IssueDate),TimeStampToDate(update.ExpiryDate)))/30