>>SQL V 6.5
>>field a = datetime-----Time portion is incorrect.
>>field b = char(5)------Has correct time for field a
>>multible records per
>>some a and/or b may be null
>>
>>select Min(CVDate(IIf(([a] & " " & [b])="",Null,[a] & " " & [b]))) AS ARRIVAL
>
>My first guess: (It's actually one whole line)
>
>select min(convert(datetime, convert(varchar(8), isnull(@a, '19000101'), 112) + isnull(@b, ' 00:00:00'))) as arrival
>
>-Mike

Well I could not get that to work, so let me post the real code with what I ended up with that does work in EM but not when I use Passthur from VFP

SELECT Incident.CUST__T,
Incident.SITE__T,
Incident.INCIDENT__T,
Incident.TICKET_NO,
Incident.INCIDENT_OFFSET_DATE as START_DATE,
Incident.INCIDENT_OFFSET_TIME as START_TIME,
CONVERT(DATETIME, CONVERT(CHAR(2),DATEPART(mm,Incident.INCIDENT_OFFSET_DATE)) + '/' + DATENAME(dd,Incident.INCIDENT_OFFSET_DATE) + '/' + DATENAME(yy,Incident.INCIDENT_OFFSET_DATE) + ' '+Incident.INCIDENT_OFFSET_TIME) AS START,
Incident.INCIDENT_CLOSE_DATE as INCIDENT_CLOSE_DATE1,
Incident.EMPLOYEES__KEY,
Incident.INCIDENT_STATUS__C,	
Incident_line.LINE,
Incident_line.CALL__T,
Incident_line.ACTION_NO,
Incident_line.CLOSE_FUNCTION,
min(convert(datetime,CONVERT(CHAR(2),DATEPART(mm,Action.ACTUAL_DATE_FROM))+ '/' + DATENAME(dd,Action.ACTUAL_DATE_FROM)+'/'+DATENAME(yy,Action.ACTUAL_DATE_FROM)+' '+ isnull(Action.ACTUAL_TIME_FROM, ' 00:00:00'))) as ARRIVAL,
Max(convert(datetime,CONVERT(CHAR(2),DATEPART(mm,Action.ACTUAL_DATE_TO))+ '/' + DATENAME(dd,Action.ACTUAL_DATE_TO)+'/'+DATENAME(yy,Action.ACTUAL_DATE_TO)+' '+ isnull(Action.ACTUAL_TIME_TO, ' 00:00:00'))) as DEPARTURE1
FROM dbo.INCIDENT Incident LEFT OUTER JOIN dbo.INCIDENT_LINE Incident_line
 ON Incident.INCIDENT__T = Incident_line.INCIDENT__T
 LEFT JOIN dbo.ACTION Action
 ON Incident_line.CALL__T = Action.CALL__T AND
 Incident_line.ACTION_NO = Action.ACTION_NO
 WHERE Incident.CUST__T = '3333'
 AND Incident.TICKET_NO IS NOT NULL
 AND Incident.TICKET_NO <> ''
 AND Upper(Incident.TICKET_NO) NOT LIKE 'W%'
 AND Upper(Incident.TICKET_NO) NOT LIKE 'M%'
GROUP BY Incident.CUST__T,
Incident.SITE__T,
Incident.INCIDENT__T,
Incident.TICKET_NO,
Incident.INCIDENT_OFFSET_DATE,
Incident.INCIDENT_OFFSET_TIME,
Incident.INCIDENT_CLOSE_DATE,
Incident.EMPLOYEES__KEY,
Incident.INCIDENT_STATUS__C,	
Incident_line.LINE,
Incident_line.CALL__T,
Incident_line.ACTION_NO,
Incident_line.CLOSE_FUNCTION,
convert(datetime,CONVERT(CHAR(2),DATEPART(mm,Action.ACTUAL_DATE_TO))+ '/' + DATENAME(dd,Action.ACTUAL_DATE_TO)+'/'+DATENAME(yy,Action.ACTUAL_DATE_TO)+' '+ isnull(Action.ACTUAL_TIME_TO, ' 00:00:00')),
convert(datetime,CONVERT(CHAR(2),DATEPART(mm,Action.ACTUAL_DATE_FROM))+ '/' + DATENAME(dd,Action.ACTUAL_DATE_FROM)+'/'+DATENAME(yy,Action.ACTUAL_DATE_FROM)+' '+ isnull(Action.ACTUAL_TIME_FROM, ' 00:00:00'))

When I use this code from VFP I get the folowing error.
Connectivity error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of CHAR to DATETIME resulted in a DATETIME value out of range.

I know I can put this in a sp but I was trying to avoid that for several unrelated reasons.

TIA for any pointers
Jeff