Datatype conversions

chulett · Post by **chulett** » Fri Aug 17, 2012 11:46 pm

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.

ArndW · Post by **ArndW** » Fri Aug 17, 2012 11:52 pm

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.

bhasds · Post by **bhasds** » Sat Aug 18, 2012 6:18 am

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

chulett · Post by **chulett** » Sat Aug 18, 2012 7:42 am

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:

Code: Select all

(year * 12) + month

Then subtract the two results.

hargun · Post by **hargun** » Sat Aug 18, 2012 8:03 am

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

hargun · Post by **hargun** » Sat Aug 18, 2012 8:18 am

Hi Arnd,

Can you provide the code that i can use in transformer to get the number of days and then compute the months

chulett · Post by **chulett** » Sat Aug 18, 2012 8:24 am

He did. At least for the "days" part, the month he left up to you.

hargun · Post by **hargun** » Sat Aug 18, 2012 10:10 am

Hi chullet,

i know he did for days being very new to datastage can you please provide the whole code .....

bhasds · Post by **bhasds** » Sat Aug 18, 2012 10:56 am

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

hargun · Post by **hargun** » Sat Aug 18, 2012 11:10 am

Thanks for providing this solution.I appreciate.This resolves.....

bhasds · Post by **bhasds** » Sat Aug 18, 2012 11:14 am

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

hargun · Post by **hargun** » Sat Aug 18, 2012 11:51 am

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

bhasds · Post by **bhasds** » Sat Aug 18, 2012 12:48 pm

Hi Hargun,

Both the code should give you the same result.So, its your wish which one to use.

chulett · Post by **chulett** » Sat Aug 18, 2012 9:14 pm

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.

bhasds · Post by **bhasds** » Sun Aug 19, 2012 3:42 am

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

DSXchange