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]"))
Improve a difference between two dates
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 56
- Joined: Mon Mar 26, 2007 8:48 am
- Location: Blue Bay (La Spezia)