https://sqlines.com/ 2026-04-29T14:55:56+00:00 https://sqlines.com/ https://sqlines.com/lib/images/favicon.ico text/html 2024-12-06T19:34:14+00:00 https://sqlines.com/informix-to-sql-server/day?rev=1733513654&do=diff In Informix and SQL Server, you can use the DAY function to get the day (an integer number) of the specified datetime. Informix: -- Get the month SELECT DAY(TO_DATE('2024-12-06', '%Y-%m-%d')) FROM systables WHERE tabid=1; /* 6 */ SQL Server: text/html 2024-12-06T11:44:58+00:00 https://sqlines.com/informix-to-sql-server/exit_for?rev=1733485498&do=diff In Informix, you can use the EXIT FOR statement to leave the FOR range loop. In SQL Server, you can use the BREAK statement. Informix: CREATE PROCEDURE loop1() DEFINE i INTEGER; -- Run a loop FOR i = 1 TO 10 -- Leave the loop IF i = 5 THEN EXIT FOR; END IF; END FOR; END PROCEDURE; text/html 2024-11-27T19:11:42+00:00 https://sqlines.com/informix-to-sql-server/float?rev=1732734702&do=diff In Informix, the FLOAT data type stores approximate double precision floating point numbers. In SQL Server you can also use the FLOAT data type. Informix allows you to specify the precision FLOAT(n), where n is a number between 1 and 14, but it is ignored. text/html 2024-12-06T11:51:33+00:00 https://sqlines.com/informix-to-sql-server/for?rev=1733485893&do=diff In Informix, you can use the FOR statement to execute a loop with the specified number of iterations. In SQL Server, you can use the WHILE statement, evaluate and increment the counter explicitly. Informix: CREATE PROCEDURE loop1() -- Loop variable has to be declared DEFINE i INTEGER; -- Run a loop FOR i = 1 TO 10 INSERT INTO items VALUES(i); -- Leave the loop IF i = 5 THEN EXIT FOR; END IF; END FOR; END PROCEDURE; text/html 2024-12-06T16:54:38+00:00 https://sqlines.com/informix-to-sql-server/foreach?rev=1733504078&do=diff In Informix, you can use the FOREACH statement to execute a query and loop over returned rows. In SQL Server, you can open a cursor for the query, and fetch all rows using the WHILE statement. Informix: -- Sample procedure CREATE PROCEDURE foreach1() -- Declare and initialize local variables DEFINE cnt INTEGER; DEFINE items INTEGER; LET cnt = 0; LET items = 0; -- Execute query and process all rows FOREACH SELECT c_items INTO items FROM items LET cnt … text/html 2024-12-06T19:25:59+00:00 https://sqlines.com/informix-to-sql-server/month?rev=1733513159&do=diff In Informix and SQL Server, you can use the MONTH function to get the month (an integer number 1-12) of the specified datetime. Informix: -- Get the month SELECT MONTH(TO_DATE('2024-12-06', '%Y-%m-%d')) FROM systables WHERE tabid=1; /* 12 */ text/html 2022-10-16T17:26:05+00:00 https://sqlines.com/informix-to-sql-server/outer?rev=1665941165&do=diff Informix supports the specific OUTER clause in the FROM clause of the SELECT statement to define the outer joins. It has to be converted to ANSI SQL outer join syntax in Microsoft SQL Server. Consider the following tables with sample data: CREATE TABLE cities (name VARCHAR(30), country VARCHAR(30)); CREATE TABLE countries (name VARCHAR(30), capital VARCHAR(30), region VARCHAR(30)); CREATE TABLE regions (name VARCHAR(30), continent VARCHAR(30)); -- Cities INSERT INTO cities VALU… text/html 2013-10-01T11:37:54+00:00 https://sqlines.com/informix-to-sql-server/return_with_resume?rev=1380627474&do=diff In Informix you can create a stored procedure that returns multiple rows using RETURN WITH RESUME statement. This allows you to execute the procedure in the FROM clause of a SELECT statement. When you convert such procedures to Microsoft SQL Server you can use a table-valued function. text/html 2024-12-05T08:33:25+00:00 https://sqlines.com/informix-to-sql-server/select_into_temp?rev=1733387605&do=diff In Informix, you can use the SELECT INTO TEMP statement to create a temporary table based on the query results. In SQL Server, you can also use the SELECT INTO statement, but the position of the INTO clause is different and the temporary table name must start with the # sign. text/html 2024-12-05T12:51:21+00:00 https://sqlines.com/informix-to-sql-server/single_double_quotes?rev=1733403081&do=diff In Informix, by default you can simultaneously use both single '' and double "" quotes for the owner names and string constants (literals), but you cannot use them for other identifiers such as table and column names. In SQL Server, by default you can use double "" quotes and [] for all identifiers, and only single '' quotes for string constants. text/html 2024-06-22T18:44:25+00:00 https://sqlines.com/informix-to-sql-server/table_exceeds_1024_columns?rev=1719081865&do=diff In SQL Server, a table can contain a maximum of 1024 columns. Informix does not have this limitation, so a source table can have more than 1024 columns: Informix: CREATE TABLE items ( c1 CHAR(1), c2 CHAR(1), c3 CHAR(1), c4 CHAR(1), c5 CHAR(1), // ... Some columns are skipped for brevity c1024 CHAR(1), c1025 CHAR(1), c1026 CHAR(1), c1027 CHAR(1) ); # Table created text/html 2024-06-22T08:59:09+00:00 https://sqlines.com/informix-to-sql-server/table_exceeds_row_size_8060?rev=1719046749&do=diff In SQL Server, a table can contain a maximum of 8,060 bytes per row excluding VARCHAR, NVARCHAR, VARBINARY and some other variable length types. Informix does not have such limitation: Informix: CREATE TABLE items ( c1 CHAR(2000), c2 CHAR(2000), c3 CHAR(2000), c4 CHAR(2000), c5 CHAR(2000) ); # Table created. text/html 2024-12-05T08:33:43+00:00 https://sqlines.com/informix-to-sql-server/today?rev=1733387623&do=diff In Informix, the TODAY function returns the current date (year, month and day). In SQL Server, you can use CONVERT(DATE, GETDATE()) expression. Informix: -- Get the current date SELECT TODAY FROM systables WHERE tabid=1; /* 12/05/2024 */