DSXchange

Hi to everyone,
I've a problem with a difference between two dates.
I have improved a lookup between an oledb stage (sserver 2005) and an hashed file previously filled in another job with data get from a sql server table.
In the transformer where I perform the lookup, I need to obtain a difference (in days) between two dates (the former is from the oledb stage and the latter from the hashed file).
The two dates have the same format, that is datetime in sql server and become timestamp in datastage. (i.e. 2007-01-15 00.00.0..)
I'm trying to use the function DateGenericDateDiff(%date1%,%date2%) but I don't obtain valuable results (only impossible date/time conversion warnings)

Could you suggest me where is the problem? Thanks a lot for any help.

I tried unsucccessfully:

DateGenericDateDiff(lnk1.startDate,lnk2.endDate)
DateGenericDateDiff(lnk1.startDate[1,10],lnk2.endDate[1,10])

And as test:
DateGenericDateDiff("20070101", "20070401")
DateGenericDateDiff(Oconv(Iconv("01012007","D MDY[2,2,4]"),"D-YMD[4,2,2]"), Oconv(Iconv("04012007","D MDY[2,2,4]"),"D-YMD[4,2,2]"))

why do you make things so complicate.

With an iconv you get any date into an integer (0= 31.12.1967) and if you calculate the one integer minus the other you get the difference in days.

In other words, just use IConv to convert both date portions to 'internal' (integer) format and subtract them - the answer will be the difference in days.

If you really want to use the 'DateGenericDateDiff' transform, it uses the 'DateGenericToTimestamp' transform to transform the date and handles quite a number of different formats as noted in its' description. Your examples should have worked fine.

Thanks a lot for your answers, I think I'll use Iconv
Only a question: are there some differences by improving the date-difference in a routine and use a transform function instead of directly in the transformer stage ? (firstly loss of performance)?

Thanks

Check this out:

viewtopic.php?t=85788

Don't worry about minor performance differences. You're going to do this type of date math a lot so use an encapsulated function.

Good, it's exactly what I need :D

Thanks a lot for your precious help.

Bye

ray.wurlod wrote:Time to mark this thread as resolved.

Yes, you're right.

bye