rhonda fischer

Hello,

I am currently taking a query used in Access and changing
it to comply with SQL Server syntax. I am experiencing
problems with the use of getDate() and the IIF function,
and as such looking for an alternative means to extract
data.

CURRENT SQL ACCESS QUERY: (works in Access)
-------------------------------------------

SELECT DeliveriesMade.[ID], DeliveriesMade.RDC,
DeliveriesMade.VehReg, DeliveriesMade.DelDate,
SQLTrailers.NextServiceDue, IIf([NextServiceDue]<Date()-
14,Yes,No) AS Overdue
FROM DeliveriesMade INNER JOIN SQLTrailers ON
DeliveriesMade.TrailerNo = SQLTrailers.FleetNumber
WHERE DeliveriesMade.DelDate = Date();

CURRENT SQL ACCESS QUERY:
-------------------------

SELECT DeliveriesMade.[ID], DeliveriesMade.RDC,
DeliveriesMade.VehReg, DeliveriesMade.DelDate,
SQLTrailers.NextServiceDue, IIf((convert(int,
NextServiceDue) < convert(int, getDate()-14)),1,0) AS
Overdue
FROM DeliveriesMade INNER JOIN SQLTrailers ON
DeliveriesMade.TrailerNo = SQLTrailers.FleetNumber
WHERE DeliveriesMade.DelDate = getDate();

=> Doesn't seem to recognise IIF function
=> ErrMsg: Line 1: Incorrect syntax near '<'
=> Not sure getDate() is returning a comparable system
date: Oct 9 2003 2:30 PM for the date value
stored in SQL 2003-10-09 02:30:00.000000000

Any ideas would be terrific.

Thank you very much
Rhonda

rhonda fischer

Hello Again,

I have since worked out what I need to replace some of
the Access SQL Query with. I just need to take the output
of my DATEDIFF a step further and convert the negative
values to a varchar "No" output or a positive value to
a varchar "yes" output where the IIF function was used
to do this, what can I use in SQL Server?

BEFORE:
------
(1) WHERE(([Deliveries Made].[Del Date])=Date())
(2) IIf([NextServiceDue]<Date()-14,Yes,No) AS Overdue

AFTER:
------
(1) WHERE CONVERT(VARCHAR, [Deliveries Made].[Del Date],
103) = CONVERT(VARCHAR, GetDate(), 103)
(2) DATEDIFF(day, NextServiceDue, CONVERT(datetime,(CAST
(getDate() as int)-14),103)) AS Overdue

SQL QUERY IN FULL
-----------------

SELECT [Deliveries Made].[ID], [Deliveries Made].RDC,
[Deliveries Made].[Veh Reg], [Deliveries Made].[Del Date],
SQLTrailers.NextServiceDue, CONVERT(varchar, CONVERT
(datetime,(CAST(getDate() as int)-14),103),120) AS
TWOWKSAGO, DATEDIFF(day, NextServiceDue, CONVERT(datetime,
(CAST(getDate() as int)-14),103)) AS Overdue
FROM [Deliveries Made] INNER JOIN SQLTrailers ON
[Deliveries Made].[Trailer No] = SQLTrailers.FleetNumber
WHERE CONVERT(VARCHAR, [Deliveries Made].[Del Date], 103)
= CONVERT(VARCHAR, GetDate(), 103)


OUTPUT OF SQL QUERY
-------------------

ID RDC Veh
Reg Del Date
NextServiceDue TWOWKSAGO
Overdue
----------- ------------------------------ ----------------
-------------- --------------------------- ----------------
----------- ------------------------------ -----------
360774 PBASINGSTOKE
ZWEBBM889 2003-10-09 00:00:00.000
2003-11-03 00:00:00.000 2003-09-26
00:00:00 -38
360775 PBASINGSTOKE R864
DBJ 2003-10-09 00:00:00.000 2003-
11-12 00:00:00.000 2003-09-26 00:00:00 -47
360776 PBASINGSTOKE ZRALPH
DAVIES 2003-10-09 00:00:00.000
NULL 2003-09-26 00:00:00
NULL