DSXchange

Need to find duration between 2 dates.
Encountered the following error while using DateGenericDateDiff:

(DateGenericToTimeStamp): Could not convert date/time :5/31/2005

I have look up the routine specs and it mentioned can be used with
"Any delimited date with Month Day Year, e.g. 4/19/1999, 4.19.1999, 4/19/99, 4.19.99"

My date format is already in MDY.
Anyone can enlighten on the error?

Thanks!

What value did you provide for the second argument?

If this is zero, the function expects the input date to be in the format (CC)YY MM DD with delimiters.
If it is one, the function expects the input date to be in the format MM DD YY with delimiters.

This message can also be generated if the input "generic date" includes an invalid time component. Your post suggests that yours lack a time component, but CAN YOU BE SURE? (That is, how good are your data? This illustrates how important it is to profile, or conduct a quality audit, on source data.)


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

My understanding of DateGenericDateDiff function is arg2 - arg1.

Thus, the second argument for DateGenericDateDiff is the later input date.

Think what you mean by zero or one is the args for DateGenericToTimeStamp. In which I did not explicitly call this method. It is called by DateGenericDateDiff. When I looked through the routine, it seems to have input zero(function expects the input date to be in the format MM DD YY with delimiters).

Any further advice?

The derivation expression for the DateGenericDateDiff Transform is:
(If Iconv(DateGenericToTimeStamp(%FirstInputDate%, 0)[1, 8], "D") # "" And Iconv(DateGenericToTimeStamp(%SecondInputDate%, 0)[1, 8], "D") # "" Then Iconv(DateGenericToTimeStamp(%FirstInputDate%, 0)[1, 8], "D") - Iconv(DateGenericToTimeStamp(%SecondInputDate%, 0)[1, 8], "D") Else "")

Output of DateGenericToTimeStamp function is a timestamp in the format YYYYMMDD HH:MM:SS.SSS

The Transform takes the first eight characters of each such timestamp and (if it's not "") converts it to an internal format (integer number of days). A better derivation expression would be:
(If Iconv(DateGenericToTimeStamp(%FirstInputDate%, 0)[1, 8], "DYMD") # "" And Iconv(DateGenericToTimeStamp(%SecondInputDate%, 0)[1, 8], "DYMD") # "" Then Iconv(DateGenericToTimeStamp(%FirstInputDate%, 0)[1, 8], "DYMD") - Iconv(DateGenericToTimeStamp(%SecondInputDate%, 0)[1, 8], "DYMD") Else "")

However, the error message you are getting is from the routine itself, which occurs only if the first argument (date component) cannot be converted using "DYMD[2,2,2]" as the conversion specification, or one of the time components is invalid (hour > 23, minute > 59 or second > 59.999).

I ran 5/31/2005 in the Test grid for the DateGenericToTimeStamp routine, and it did not generate the error.

Can you therefore check your data to see whether there are any non-printing characters in it in the row that generated the error?

Read from the routine comments that DateGenericToTimeStamp might not work in a NLS environment. I manage to get it working after disabling the NLS maps for this job. Thanks Ray!

However, the calculation returns results in number of days, any way to return in months OR convert the number of days to months(accurate to convert this way?) ?

Thanks in advance! [:)]

Not really, unless you're using a calendar with the same number of days in every month.
A better solution is a routine that actually works in months. Ken Bland posted one not a long time back; can't remember whether that was on this list or another one. If I spot it as I check through the lists I will post it, or a reference to it, here.