Incorrect syntax near '<'

sql-server tsql sql-server-2012 sql-server-2008-r2 iif
Stefan picture Stefan · Sep 2, 2016 · Viewed 8.2k times · Source

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

Shnugo picture Shnugo · Sep 2, 2016

There was no IIF in SQL Server 2008R2.

Replace it with CASE

ALTER TABLE [dbo].[WorkTimeEntries] ADD [TimeFinishedForRuntime] AS ISNULL(
    [TimeFinished],
    CASE WHEN [TimeStarted] < SYSUTCDATETIME() THEN [dbo].[udf_GetCurrentDateTimeOffsetInTimeZone](DATENAME(TZOFFSET, [TimeStarted])) ELSE [TimeStarted] END);

Related questions

How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
Count the Number of Tables in a SQL Server Database

I have a SQL Server 2012 database called MyDatabase. How can I find how many tables are in the database? I'm assuming the format of the query would be something like the following, but I don't know what to replace database_…

Read More