IIF in Select Query Error

[mcalpin]

Hello,

I need to create a query with a date calculation of elapsed minutes. And, I
have made this work. Now I find that some of the calculations come up with
large negative numbers since one of the values may be "1800-01-01 00:00:00.0".
I am attempting to add the IIF syntax to make the resultant value equal zero if
the calculation was a negative number. But, I am getting a syntax violation at
the "<" character. Any ideas?

Thanks!
-------------------------------------------


<cfquery name="qryGetDay" datasource="MyDatasource">
SELECT RADIONAME, DISPDATE, DISPDATE, RTQDate, IIF(DateDiff("n", DISPDATE,
RTQDate) < 0, 0, DateDiff("n", DISPDATE, RTQDate)) AS TotalMins
FROM DBTable
WHERE DISPDATE BETWEEN #CreateODBCDateTime(GetDateBegin)# AND
#CreateODBCDateTime(GetDateEnd)#
AND RADIONAME IN ('#Replace(UnitList, ",", "','", "All")#')
ORDER BY RADIONAME
</cfquery>

[TA-Selene]

The problem is that IIf is a ColdFusion function. You need to use logic that
works with your database. If you are using SQL Server, the following should
work instead:

CASE WHEN DateDiff(minute, DISPDATE, RTQDate) < 0 THEN 0 ELSE DateDiff(minute,
DISPDATE, RTQDate) END

Luckily, SQL Server has a DateDiff function as well.

[mcalpin]

That worked (the crowd goes wild). Thanks a lot! John