Marcia,

I'd either create an additional T field in the table, set that once and normalization be damned for the moment or create a function and call that always in the SQL - deciding by size of table re slowdown through multiple function call which stinks less ;-)

Too much technical debt entering here via the decision to use a date and a time field - as that is possibly at the fwk level, make the client an offer to fix either for normalized again or live with added duration (in his app and/or your work), even if it is only copy&paste effort.



>Hi all.
>
>I have beat my head on the keyboard for long enough. Here is the query (angle brackets have been replaced by square brackets for readability here)
>
>

>TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 15
>    SELECT DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) AS start_time, 
>	DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) + ((VAL(GETWORDNUM(VEH.duration, 1, ':')) * 3600) + (VAL(GETWORDNUM(VEH.duration, 2, ':')) * 60)) AS end_time, 
>	ST.descr AS SvcType_dsc, SVC.servref 
>    FROM funeral!ServiceVehicle VEH WITH (BUFFERING = .T.) 
>	JOIN aserdet SVC WITH (BUFFERING = .T.) ON VEH.service_id = SVC.serviceid 
>	JOIN psvctype ST ON SVC.servtype = ST.code 
>	WHERE VEH.VehCode =  '[[tcVehCode]]'  
>		AND VEH.SvcVehID # [[tiSvcVehID]]  
>		AND DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) < {[[ttEndTime]]} 
>		AND DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) + ((VAL(GETWORDNUM(VEH.duration, 1, ':')) * 3600) + (VAL(GETWORDNUM(VEH.duration, 2, ':')) * 60)) > {[[ttStartTime]]}
>ENDTEXT 
>

>
>It hands me back Function argument value, type, or count is invalid.
>
>Now the goofy thing is that this one works:
>
>

>TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 15
>    SELECT DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) AS start_time, 
>	DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) + ((VAL(GETWORDNUM(VEH.duration, 1, ':')) * 3600) + (VAL(GETWORDNUM(VEH.duration, 2, ':')) * 60)) AS end_time, 
>	ST.descr AS SvcType_dsc, SVC.servref 
>    FROM funeral!ServiceVehicle VEH WITH (BUFFERING = .T.) 
>	JOIN aserdet SVC WITH (BUFFERING = .T.) ON VEH.service_id = SVC.serviceid 
>	JOIN psvctype ST ON SVC.servtype = ST.code 
>	WHERE VEH.VehCode =  '[[tcVehCode]]'  
>		AND VEH.SvcVehID # [[tiSvcVehID]] 
>		AND DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) < {[[ttEndTime]]} 
>ENDTEXT 
>

>
>and this one works:
>

>TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 15
>    SELECT DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) AS start_time, 
>	DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) + ((VAL(GETWORDNUM(VEH.duration, 1, ':')) * 3600) + (VAL(GETWORDNUM(VEH.duration, 2, ':')) * 60)) AS end_time, 
>	ST.descr AS SvcType_dsc, SVC.servref 
>    FROM funeral!ServiceVehicle VEH WITH (BUFFERING = .T.) 
>	JOIN aserdet SVC WITH (BUFFERING = .T.) ON VEH.service_id = SVC.serviceid 
>	JOIN psvctype ST ON SVC.servtype = ST.code 
>	WHERE VEH.VehCode =  '[[tcVehCode]]'  
>		AND VEH.SvcVehID # [[tiSvcVehID]] 
>		AND DATETIME(YEAR(VEH.EvtDate), MONTH(VEH.EvtDate), DAY(VEH.EvtDate), VAL(GETWORDNUM(VEH.EvtTime, 1, ":")), VAL(GETWORDNUM(VEH.EvtTime, 2, ":"))) + ((VAL(GETWORDNUM(VEH.duration, 1, ':')) * 3600) + (VAL(GETWORDNUM(VEH.duration, 2, ':')) * 60)) > {[[ttStartTime]]}
>ENDTEXT 
>

>
>So why doesn't it work with both the start time and the end time in the where clause?
>
>TIA