Incorrect syntax near '<'

Question

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.

What SQL version are you running this on -- 2008 or 2012? IIF() was not introduced until 2012. — Dave C, Sep 02 '16 at 20:14
I edit my post, my English is not good sorry. It's work good on SQL 2012, it's can't work on SQL 2008 R2. — Stefan, Sep 02 '16 at 20:20

score 8 · Accepted Answer · answered Sep 02 '16 at 20:17

8

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);

answered Sep 02 '16 at 20:17

Shnugo

66,100
9
53
114

Oh thanks it works, but now i get another one error and i think its not a last one. < Incorrect syntax near 'THROW'. in this line `BEGIN; THROW 50003, 'Inconsistent employee ids have been provided for TimeRegisteredEmployees', 1; END;` – Stefan Sep 02 '16 at 20:48
@Stefan, same here... `THROW` was introduced with SQL Server 2012. Before `RAISERROR` was used. Find [details and a comparison here](https://msdn.microsoft.com/en-us/library/ee677615.aspx) – Shnugo Sep 03 '16 at 08:37
With your help i correct all errors without this one which I get on the end of my work >Incorrect syntax near 'ROWS'. Whick take place in this line ~ISNULL(SUM(aed.[ActualMultiple]) OVER (PARTITION BY [WorkItemId] ORDER BY [TimeFinishedForRuntime] ROWS UNBOUNDED PRECEDING), 0) AS [GrossMultiple] FROM [dbo].[BasicAggregatedWorkTimeEntriesView] wte LEFT OUTER JOIN [AggregatedEmployeeData] aed ON wte.[Id] = aed.[WorkTimeEntryId];~ What i found out is that the ROWS are from SQL SERVER 2012 but i don't know how to replace it in my case. – Stefan Sep 05 '16 at 10:15
@Stefan, here you are in troubles... the `SUM(...) OVER()` is used to calculate a cummulative SUM. This feature is quite new also... You might use something like `TimeFinishedForRuntime + (SELECT SUM(x.TimeFinishedForRuntime FROM dbo.WorkTimeEntries AS x WHERE xTimeStarted>TimeStarted))` Doing so, you would get for each of your rows a sum of all the older rows added to the actual value. But this is blind guessing... – Shnugo Sep 05 '16 at 10:31
We have to sum by Actual Multiple and i think We need there correlation with WorkItemId inside of WHERE. – Stefan Sep 05 '16 at 10:43

score -1 · Answer 2 · answered Dec 18 '20 at 16:06

-1

Basically, this error occurs if you any syntactical error in your SQL code. Please reverify all your statement sequence.

answered Dec 18 '20 at 16:06

yuvraj

181
1
12

Incorrect syntax near '<'

2 Answers2

Linked