Datatype conversions
Moderators: chulett, rschirm, roy
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).
Will give you the number of days between the two dates. From there you can compute the months.
Code: Select all
DaysSinceFromDate(TimeStampToDate(issue_dt),TimeStampToDate(expiry_dt))
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
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))
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.
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:
Code: Select all
(year * 12) + month
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
hi ,bhasds wrote:Code: Select all
Neg(MonthFromDate(issue_dt)- MonthFromDate(expiry_dt))
i have tried with this conversion but while compling the job getting the error of invalid converison from timestamp to date
Datatype conversions
Hi Hargun,
The below code should work if you have timestamp in source.
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
Thanks for providing this solution.I appreciate.This resolves.....
Datatype conversions
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 .
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
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
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
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?
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