I've got a piece of SQL that works just fine in BTEQ but alas, has the dreaded "5407 Invalid operation on an ANSI date time value" error when run through ODBC. I'm pretty sure I just need to wrap the correct cast statement around it, but so far all my efforts have failed. The code is below and the variable causing the problem is the calculation of the number of seconds from the start to the finish of a job. (Total_Sec_Diff)
select L.UserName ,L.LogDate ,L.TotalCPUtime ,(L.LastRespTime - L.StartTime) DAY(4) TO SECOND as TotalDiff ,(extract(day from TotalDiff) * 86400) + (extract(hour from TotalDiff) * 3600) + (extract(minute from TotalDiff) * 60) + (extract(second from TotalDiff)) as Total_Sec_Diff
Where L.NumResultRows <> 0 and L.LogDate >= date '2006-10-01'
Well after a few tries I discovered that the last cast to interval second returns 6 places of precision. Once I cared for that this version produces correct results in ODBC.
,(L.LastRespTime - L.StartTime) DAY(4) TO SECOND as TotalDiff
, cast ( (cast(TotalDiff as interval day) * 86400 ) as Dec(9,6)) + cast ( (cast(TotalDiff as interval hour) * 3600 ) as Dec(9,6)) + cast ( (cast(TotalDiff as interval minute) * 60 ) as Dec(9,6)) + cast ( (cast(TotalDiff as interval second ) ) as Dec(9,6)) as Total_Sec_Diff
Some times you just need someone to listen..... (or read in this case) :-)