Developer ToolsFree referencePDF, Excel, Word exportSAP Business OneAll SAP Business One tools

SAP Business One T-SQL to HANA SQL Query Conversion Reference

This reference maps the T-SQL you wrote for SAP Business One on Microsoft SQL Server to the HANA SQL that SAP Business One version for SAP HANA expects. It covers saved queries, formatted searches and the transaction notification procedure.

Download:

T-SQL (SQL Server)	HANA SQL equivalent	B1 query fragment (HANA)	Gotcha
[OITM].[ItemCode] or T0.ItemCode	"OITM"."ItemCode" or T0."ItemCode"	SELECT T0."ItemCode", T0."ItemName" FROM OITM T0	Double-quoted names are case-sensitive. Unquoted names are folded to upper case, so T0.ItemCode looks for ITEMCODE and fails. B1 table names are upper case and work unquoted; mixed-case column names need quotes.
ISNULL(x, y)	IFNULL(x, y)	IFNULL(T0."OnHand", 0)	ISNULL does not exist in HANA. COALESCE works in both and accepts more than two arguments.
GETDATE()	CURRENT_TIMESTAMP	WHERE T0."CreateDate" <= CURRENT_TIMESTAMP	Includes the time of day. B1 date columns such as DocDate hold midnight, so an equality test against CURRENT_TIMESTAMP never matches; use CURRENT_DATE for date-only logic.
CAST(GETDATE() AS DATE)	CURRENT_DATE	WHERE T0."DocDueDate" < CURRENT_DATE	Server date of the HANA host, not the client PC.
DATEADD(day, n, d)	ADD_DAYS(d, n)	ADD_DAYS(T0."DocDueDate", 30)	Argument order is reversed: date first, then the number.
DATEADD(month, n, d)	ADD_MONTHS(d, n)	WHERE T0."DocDate" >= ADD_MONTHS(CURRENT_DATE, -12)	Date first. Use a negative number to go back in time.
DATEDIFF(day, a, b)	DAYS_BETWEEN(a, b)	DAYS_BETWEEN(T0."DocDueDate", CURRENT_DATE) AS "DaysOverdue"	Same order as T-SQL (start, end); positive when the second date is later.
DATEDIFF(month, a, b)	MONTHS_BETWEEN(a, b)	MONTHS_BETWEEN(T0."DocDate", CURRENT_DATE)	T-SQL counts month boundaries crossed; check HANA results around month ends against a few known dates before trusting aging buckets.
CONVERT(varchar(8), d, 112)	TO_VARCHAR(d, 'YYYYMMDD')	TO_VARCHAR(T0."DocDate", 'YYYYMMDD')	Style numbers (112, 103, 120) do not exist in HANA; write the format string, e.g. 'DD/MM/YYYY'.
Date literal '20261022'	'2026-10-22' or TO_DATE('2026-10-22', 'YYYY-MM-DD')	WHERE T0."DocDate" >= TO_DATE('2026-01-01', 'YYYY-MM-DD')	Write dates in ISO form or convert explicitly; query parameters such as [%0] avoid literals altogether.
CAST(x AS INT)	CAST(x AS INTEGER)	CAST(T0."U_Width" AS INTEGER)	CAST works the same way; conversion of a non-numeric string raises an error in both, so clean UDF data first.
LEN(s)	LENGTH(s)	WHERE LENGTH(T0."ItemCode") > 10	T-SQL LEN ignores trailing spaces; LENGTH counts them.
CHARINDEX(find, s)	LOCATE(s, find)	LOCATE(T0."ItemName", '-')	Argument order is reversed: the string to search comes first in LOCATE. Returns 0 when not found in both.
a + b (string concatenation)	a \|\| b or CONCAT(a, b)	T0."CardCode" \|\| ' - ' \|\| T0."CardName"	Using + on strings makes HANA attempt a numeric conversion and fail. HANA CONCAT takes exactly two arguments; chain \|\| for more.
LEFT(s, n) / RIGHT(s, n)	LEFT(s, n) / RIGHT(s, n)	LEFT(T0."ItemCode", 3)	Same syntax. Common in item-prefix reports.
SUBSTRING(s, start, len)	SUBSTRING(s, start, len)	SUBSTRING(T0."ItemCode", 4, 5)	Same syntax, 1-based start position.
YEAR(d), MONTH(d)	YEAR(d), MONTH(d)	GROUP BY YEAR(T0."DocDate"), MONTH(T0."DocDate")	Same names. Every non-aggregated SELECT expression must also be in GROUP BY.
DAY(d)	DAYOFMONTH(d)	DAYOFMONTH(T0."DocDueDate")	Use DAYOFMONTH; EXTRACT(DAY FROM d) also works.
DATEPART(week, d)	WEEK(d) or ISOWEEK(d)	ISOWEEK(T0."DocDate")	T-SQL week depends on SET DATEFIRST. ISOWEEK returns a string such as 2026-W43, not a number.
DATENAME(weekday, d)	DAYNAME(d)	DAYNAME(T0."DocDate")	Returns the English name in upper case, e.g. MONDAY. WEEKDAY(d) returns 0 for Monday to 6 for Sunday.
SELECT TOP 10 ...	SELECT TOP 10 ... or ... LIMIT 10	SELECT TOP 10 T0."CardCode", T0."DocTotal" FROM OINV T0 ORDER BY T0."DocTotal" DESC	Both forms are valid in HANA. Without ORDER BY the rows returned are arbitrary.
SELECT 1 (no FROM)	SELECT 1 FROM DUMMY	SELECT ADD_DAYS(CURRENT_DATE, 7) FROM DUMMY	Every HANA SELECT needs a FROM clause; DUMMY is the one-row system table.
Formatted search: SELECT $[$38.1.0]	SELECT $[$38.1.0] FROM DUMMY	SELECT T0."ItemName" FROM OITM T0 WHERE T0."ItemCode" = $[$38.1.0]	The $[...] field reference is resolved by B1 before execution and stays the same; the FROM DUMMY rule and column quoting still apply.
WHERE x = 'abc' (case-insensitive)	WHERE UPPER(x) = UPPER('abc')	WHERE UPPER(T0."CardName") LIKE UPPER('%steel%')	B1 SQL Server databases normally use a case-insensitive collation; HANA string comparison is case-sensitive, so searches silently return fewer rows.
a / b	a / NULLIF(b, 0)	T0."GrosProfit" / NULLIF(T0."DocTotal", 0) * 100	HANA stops with SQL error 304 division by zero. A WHERE b <> 0 filter is not a guarantee because predicate order is not fixed; guard the divisor itself.
CASE WHEN ... END	CASE WHEN ... END	CASE T0."DocStatus" WHEN 'O' THEN 'Open' ELSE 'Closed' END	Same syntax. All branches must return compatible types.
IIF(cond, a, b)	CASE WHEN cond THEN a ELSE b END, or MAP(expr, v1, r1, default)	MAP(T0."CANCELED", 'Y', 'Cancelled', 'Active')	IIF does not exist in HANA. MAP compares one expression with a list of values.
COALESCE(a, b, c)	COALESCE(a, b, c)	COALESCE(T1."Price", T2."Price", 0)	Same syntax; the portable choice for both databases.
CEILING(x)	CEIL(x)	CEIL(T0."Quantity" / NULLIF(T1."SalPackUn", 0))	CEILING is not a HANA function name.
RIGHT(REPLICATE('0', 8) + x, 8)	LPAD(x, 8, '0')	LPAD(TO_VARCHAR(T0."DocNum"), 8, '0')	LPAD pads on the left to the given length; convert numbers to strings first.
STUFF((SELECT ',' + x ... FOR XML PATH('')), 1, 1, '')	STRING_AGG(x, ',' ORDER BY x)	SELECT T0."DocEntry", STRING_AGG(T1."ItemCode", ', ') FROM ORDR T0 INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry" GROUP BY T0."DocEntry"	Needs GROUP BY on the other columns. Much simpler than the XML trick.
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)	ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)	ROW_NUMBER() OVER (PARTITION BY T0."CardCode" ORDER BY T0."DocDate" DESC)	Same syntax; filter on it from an outer query, not in the same WHERE.
#temp tables, several statements	One SELECT with a subquery or WITH clause	WITH S AS (SELECT "ItemCode", SUM("OnHand") AS "Qty" FROM OITW GROUP BY "ItemCode") SELECT * FROM S WHERE "Qty" > 0	The Query Manager runs a single statement; rewrite multi-step scripts as one query or move them to a stored procedure.
DECLARE @d DATETIME / SET @d = ...	B1 query parameter in the WHERE clause	WHERE T0."DocDate" >= [%0] AND T0."DocDate" <= [%1]	@variables are not supported in a HANA Query Manager query. Remove the DECLARE trick used on SQL Server and reference the column directly.
EXEC dbo.MyProc @p = 1	CALL "MYSCHEMA"."MYPROC"(1)	CALL "SBODEMOUS"."Z_STOCK_SNAPSHOT"(1)	Procedures are written in SQLScript and called with CALL; schema and name are case-sensitive when quoted.
SBO_SP_TransactionNotification in T-SQL	SBO_SP_TransactionNotification in SQLScript	IF :object_type = '17' AND :transaction_type = 'A' THEN ... error := 1; error_message := 'Customer ref required'; END IF;	Variables are read with a colon (:error), assigned with :=, and the procedure ends with SELECT :error, :error_message FROM DUMMY. Rewrite each block; do not paste T-SQL.

SAP Business One for HANA runs each company as its own schema, and the Query Manager executes against the current company schema, so OITM resolves to that company's table. Test every converted query in Query Manager on a copy of the company before replacing the SQL Server version.

Send us your query library

Export your saved queries from OUQR and we will review the conversion to HANA SQL and flag the ones that will fail.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Search the table for the construct that failed, for example DATEADD, ISNULL or CHARINDEX, then copy the HANA form and check the gotcha column. Most migrated queries break on three things: unquoted mixed-case column names, missing FROM DUMMY in formatted searches and argument order in the date and string functions. Work through a saved query top to bottom: quote every column, replace functions, then run it. Export the table to Excel and add a status column if you are converting a whole query library during a SQL to HANA migration.

Quote every column: T0."CardCode", not T0.CardCode.
Add FROM DUMMY to any SELECT without a table.
Swap date argument order: ADD_DAYS(date, n).
Guard every division with NULLIF.

Where this lives in SAP Business One

Saved queries live in the Query Manager (Tools > Queries > Query Manager) and are stored in table OUQR, so after a migration the old T-SQL text is still there and fails the first time a user runs it. Formatted searches (FMS) are linked to fields in CSHS and point at a saved query, so a broken query breaks the field silently. Print layouts, Crystal Reports and add-ons carry their own SQL and need the same conversion. The SBO_SP_TransactionNotification and SBO_SP_PostTransactionNotice procedures must be rewritten in SQLScript inside the company schema.

Worked example: overdue invoices

On SQL Server a typical aging query reads SELECT T0.CardCode, DATEDIFF(day, T0.DocDueDate, GETDATE()) FROM OINV T0 WHERE T0.DocStatus = 'O'. On HANA it becomes SELECT T0."CardCode", DAYS_BETWEEN(T0."DocDueDate", CURRENT_DATE) AS "DaysOverdue" FROM OINV T0 WHERE T0."DocStatus" = 'O'. Notice that DAYS_BETWEEN keeps the start, end order of DATEDIFF, while ADD_DAYS and LOCATE reverse their T-SQL counterparts. If the query then calculates a percentage of DocTotal, wrap the divisor in NULLIF(T0."DocTotal", 0) so a zero-value invoice does not stop the whole report with error 304.

Version differences and gotchas

String comparisons are the quiet failure: a SQL Server company usually uses a case-insensitive collation, so WHERE CardName LIKE '%steel%' matched STEEL; on HANA it does not, and the report simply shows fewer rows. Use UPPER on both sides. Trailing spaces also behave differently because LENGTH counts them and LEN does not. Query parameters [%0] still work, but the DECLARE and SET tricks some consultants used to name parameters on SQL Server do not. Finally, user-defined fields keep their U_ prefix and mixed case, so they need quotes too.

Frequently Asked Questions

Why does my SAP Business One query work on SQL Server but fail on HANA?

Usually because column names are not quoted. HANA folds unquoted identifiers to upper case, so T0.ItemCode becomes ITEMCODE and is not found. The other common causes are T-SQL-only functions such as ISNULL, GETDATE and DATEADD, and a SELECT without FROM in a formatted search.

How do I fix the SAP HANA division by zero error in a B1 query?

HANA raises SQL error 304 when a divisor is zero. Wrap the divisor in NULLIF(column, 0) so the result becomes NULL instead, or use CASE WHEN column = 0 THEN 0 ELSE a / column END. Do not rely on a WHERE filter to exclude zero rows, because HANA does not guarantee predicate order.

How do I write a formatted search (busqueda formateada) for SAP Business One on HANA?

Keep the field reference such as $[$38.1.0] exactly as on SQL Server, quote the column names and add FROM DUMMY when the query has no table, for example SELECT $[$38.1.0] FROM DUMMY. If it reads a table, use SELECT T0."ItemName" FROM OITM T0 WHERE T0."ItemCode" = $[$38.1.0].

Is SAP Business One on HANA faster than on SQL Server?

Analytical queries over large document tables usually run faster on HANA, and some features such as Pervasive Analytics need it. For small companies doing transactional work the difference is less visible. The real cost is migration effort: every saved query, formatted search, layout and add-on SQL must be converted and retested.

If you want your team to ask SAP Business One questions in plain English and get the HANA query shown with the answer, on your own servers, we can set that up.

Talk to an Expert Explore More Free Tools