Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server?


Access SQL

• UPDATE Books INNER JOIN UpdateList
     ON Books.ISBN = UpdateList.ISBN
     SET Books.Price = [Updatelist].[price]

SQL Server

• UPDATE Books
      SET Books.Price = [Updatelist].[price]   
      FROM Books INNER JOIN UpdateList
         ON Books.ISBN = UpdateList.ISBN

Access InStr() function to SQL Server equivalent

What is the SQL Server equivalent to Access InStr() function?

TargetField: String expression being searched

SearchValue: String expression being sought

StartPosition: Starting position for each search (optional)

Access SQL

• InStr(TargetField, SearchValue)
• InStr(StartPosition, TargetField, SearchValue)

SQL Server

• CHARINDEX(SearchValue, TargetField)
• CHARINDEX(SearchValue, TargetField, StartPosition )

Access IIf() function to SQL Server equivalent

What is the SQL Server equivalent to Access IIf() function?


Access SQL

• IIf([ResultValue]>=1, [Result Value], Null)

SQL Server

•  CASE WHEN [Result Value]>=1
•    THEN [Result Value]
•    ELSE NULL 
•  END 

Access NZ() function to SQL Server equivalent

What is the SQL Server equivalent to Access NZ() function?


Access SQL

• NZ([Event Timestamp])

SQL Server Equivalent

• COALESCE([Event Timestamp],0)
• ISNULL([Event Timestamp],0)
• do not confuse this with the Access isnull() function.

Access CDate() function to SQL Server equivalent

What is the SQL Server equivalent to Access CDate() function?


Access SQL

• CDate([Event Timestamp])

SQL Server Equivalent

• cast([Event Timestamp] as datetime)
• convert(datetime, [Event Timestamp])

Access DateValue() function to SQL Server equivalent

What is the SQL Server equivalent to Access DateValue() function?


Access SQL

• DateValue([Event Timestamp])

SQL Server Equivalent

• cast([Event Timestamp] as date)
• convert(date, [Event Timestamp])