Improve a difference between two dates

Alethesnake · Post by **Alethesnake** » Thu Apr 12, 2007 4:34 am

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

WoMaWil · Post by **WoMaWil** » Thu Apr 12, 2007 5:52 am

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.

chulett · Post by **chulett** » Thu Apr 12, 2007 6:49 am

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.

Alethesnake · Post by **Alethesnake** » Thu Apr 12, 2007 11:09 am

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

kcbland · Post by **kcbland** » Thu Apr 12, 2007 12:32 pm

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.

Alethesnake · Post by **Alethesnake** » Fri Apr 13, 2007 2:32 am

Good, it's exactly what I need :D

Thanks a lot for your precious help.

Bye

ray.wurlod · Post by **ray.wurlod** » Fri Apr 13, 2007 3:05 pm

Time to mark this thread as resolved.

Alethesnake · Post by **Alethesnake** » Fri Apr 13, 2007 5:23 pm

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

Yes, you're right.

bye