Stefan Stefan - 1 year ago 77
SQL Question

Incorrect syntax near '<'

I have a task to get some code which is working correctly on SQL Server 2012 to work on SQL Server 2008 R2 as well. I got this error:

Additional information: Incorrect syntax near '<'

When I try to run my code I found out that something is wrong in this line of my SQL code

ALTER TABLE [dbo].[WorkTimeEntries]
ADD [TimeFinishedForRuntime] AS ISNULL([TimeFinished],
IIF ([TimeStarted] < SYSUTCDATETIME(), [dbo].[udf_GetCurrentDateTimeOffsetInTimeZone](DATENAME(TZOFFSET, [TimeStarted])), [TimeStarted]));

I have read that in this cases took place some kind of error when people try to get date, but I'm not sure what's wrong in my case.

Answer Source

There was no IIF in SQL Server 2008R2.

Replace it with CASE

ALTER TABLE [dbo].[WorkTimeEntries] ADD [TimeFinishedForRuntime] AS ISNULL(
    CASE WHEN [TimeStarted] < SYSUTCDATETIME() THEN [dbo].[udf_GetCurrentDateTimeOffsetInTimeZone](DATENAME(TZOFFSET, [TimeStarted])) ELSE [TimeStarted] END);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download