DataStage functions, translated to Databricks, Fabric & Snowflake
A working reference for migration engineers: 126 IBM DataStage BASIC & Transformer functions (178 names and aliases) mapped to their Spark SQL, Microsoft Fabric, and Snowflake equivalents — with edge-case notes wherever the semantics differ. Generated from the PipelineX migration engine.
126 entries
Catalog mappings are review aids. SQL dialect and runtime context matter; inspect every note and test the actual generated expression.
| DataStage function | Databricks | Fabric | Snowflake | Review notes |
|---|---|---|---|---|
| AbsMath Also: fabs | ABS(…) | ABS(…) | ABS(…) | Check behaviour in your runtime. |
| AddDaysDate & Time | DATE_ADD(a, b) | DATEADD(DAY, b, a) | DATEADD(DAY, b, a) | Check behaviour in your runtime. |
| AddMonthsDate & Time | ADD_MONTHS(a, b) | DATEADD(MONTH, b, a) | DATEADD(MONTH, b, a) | Check behaviour in your runtime. |
| AddYearsDate & Time | ADD_MONTHS(a, b * 12) | DATEADD(YEAR, b, a) | DATEADD(YEAR, b, a) | Check behaviour in your runtime. |
| AlnumString | (a RLIKE '^[A-Za-z0-9]+$') | (a NOT LIKE '%[^A-Za-z0-9]%') | (a RLIKE '^[A-Za-z0-9]+$') | Check behaviour in your runtime. |
| AlphaString | (a RLIKE '^[A-Za-z]+$') | (a NOT LIKE '%[^A-Za-z]%') | (a RLIKE '^[A-Za-z]+$') | Check behaviour in your runtime. |
| AsDoubleCast | Review required | CAST(a AS FLOAT) | CAST(a AS DOUBLE) | Check behaviour in your runtime. |
| AsFloatCast | Review required | Review required | Review required | Check behaviour in your runtime. |
| AsIntegerCast | Review required | CAST(a AS INT) | CAST(a AS INT) | Check behaviour in your runtime. |
| BITANDBitwise | (a & b) | (a & b) | BITAND(a, b) | Check behaviour in your runtime. |
| BITCOMPRESSBitwise | LPAD(CONV(a, 10, 2), 8 * LENGTH(a), '0') | CAST(a AS VARCHAR) | TO_CHAR(a) | Check behaviour in your runtime.Semantic differences |
| BITEXPANDBitwise | CAST(CONV(a, 2, 10) AS STRING) | CAST(a AS VARCHAR) | TO_CHAR(a) | Bit expand/compress are approximate outside Spark.Semantic differences |
| BITNOTBitwise | (~ a) | (~ a) | BITNOT(a) | Check behaviour in your runtime. |
| BITORBitwise | (a | b) | (a | b) | BITOR(a, b) | Check behaviour in your runtime. |
| BITSHIFTBitwise | SHIFTLEFT(a, b) | (a * POWER(2, b)) | BITSHIFTLEFT(a, b) | Check behaviour in your runtime. |
| BITXORBitwise | (a ^ b) | (a ^ b) | BITXOR(a, b) | Check behaviour in your runtime. |
| CeilMath Also: Ceiling | CEIL(…) | CEILING(a) | CEIL(…) | Check behaviour in your runtime. |
| CharString | CHR(…) | CHAR(a) | CHR(…) | Check behaviour in your runtime. |
| ChecksumKey Also: Cheksum | Review required | Review required | Review required | 0-arg Checksum() hashes the whole row — plug in the column list.Context-dependent |
| ColumnImportUtil | Review required | Review required | Review required | ColumnImport parses sub-columns from a delimited field — review. |
| CompactWhiteSpaceString | REGEXP_REPLACE(a, '\\s+', ' ') | a | REGEXP_REPLACE(a, '\\s+', ' ') | T-SQL Warehouse has no regex; whitespace compaction needs a UDF. |
| ComparNoCaseString Also: CompareNoCase | Review required | Review required | Review required | Check behaviour in your runtime. |
| ComparNumeString Also: CompareNum | Review required | Review required | Review required | Check behaviour in your runtime. |
| ComparNumNoCaseString Also: CompareNumNoCase | Review required | Review required | Review required | Check behaviour in your runtime. |
| ConvertString | TRANSLATE(c, a, b) | TRANSLATE(c, a, b) | TRANSLATE(c, a, b) | DataStage Convert(from, to, string) → TRANSLATE(string, from, to). Char-for-char replacement; differing from/to lengths behave like deletion in DataStage but not in SQL TRANSLATE — review. |
| CountString | ((LENGTH(a) - LENGTH(REPLACE(a, b, ''))) / NULLIF(LENGTH(b),0)) | ((LEN(a) - LEN(REPLACE(a, b, ''))) / NULLIF(LEN(b),0)) | REGEXP_COUNT(a, b) | DataStage Count(string, substring) returns # of occurrences. |
| CurrentDateDate & Time | CURRENT_DATE() | CAST(GETDATE() AS DATE) | CURRENT_DATE() | Check behaviour in your runtime. |
| CurrentTimeDate & Time | DATE_FORMAT(CURRENT_TIMESTAMP(), 'HH:mm:ss') | CAST(SYSDATETIME() AS TIME) | CURRENT_TIME() | Check behaviour in your runtime. |
| CurrentTimeMSDate & Time | UNIX_TIMESTAMP(CURRENT_TIMESTAMP()) * 1000 | DATEDIFF_BIG(MILLISECOND, '1970-01-01', SYSUTCDATETIME()) | DATE_PART(EPOCH_MILLISECOND, CURRENT_TIMESTAMP()) | Check behaviour in your runtime. |
| CurrentTimestampDate & Time Also: TimeDate | CURRENT_TIMESTAMP() | SYSDATETIME() | CURRENT_TIMESTAMP() | Check behaviour in your runtime. |
| DateFromComponentsDate & Time | MAKE_DATE(a, b, c) | DATEFROMPARTS(a, b, c) | DATE_FROM_PARTS(a, b, c) | Check behaviour in your runtime. |
| DateFromDaysSinceDate & Time | DATE_ADD(b, a) | DATEADD(DAY, a, b) | DATEADD(DAY, a, b) | DateFromDaysSince(days, base) → base + days. When base is omitted DataStage uses 1900-01-01. |
| DateFromJulianDayDate & Time | FROM_UNIXTIME(((a - 2440588) * 86400), 'yyyy-MM-dd') | DATEADD(DAY, a - 2440588, '1970-01-01') | DATEADD(DAY, a - 2440588, '1970-01-01'::DATE) | Julian Day Number 2440588 = 1970-01-01.Semantic differences |
| DateOffsetByComponentsDate & Time | a + INTERVAL b YEAR + INTERVAL c MONTH + INTERVAL d DAY | DATEADD(DAY, d, DATEADD(MONTH, c, DATEADD(YEAR, b, a))) | DATEADD(DAY, d, DATEADD(MONTH, c, DATEADD(YEAR, b, a))) | Check behaviour in your runtime. |
| DateToDecimalDate & Time | CAST(DATE_FORMAT(a, 'yyyyMMdd') AS DECIMAL(8,0)) | CAST(FORMAT(a, 'yyyyMMdd') AS DECIMAL(8,0)) | CAST(TO_CHAR(a, 'YYYYMMDD') AS DECIMAL(8,0)) | Check behaviour in your runtime. |
| DateToStringDate & Time | Review required | Review required | Review required | DataStage date format codes differ from Spark/Snowflake patterns; 1-arg form assumes yyyyMMdd. Review the format mask.Semantic differencesContext-dependent |
| DayFromDateDate & Time Also: MonthDayFromDate | Review required | Review required | Review required | Check behaviour in your runtime. |
| DaysSinceFromDateDate & Time Also: DaysSinceFromDate2 | DATEDIFF(a, b) | DATEDIFF(DAY, b, a) | DATEDIFF(DAY, b, a) | DataStage DaysSinceFromDate(source, given) → days from given to source. Spark DATEDIFF(end, start); T-SQL/Snowflake DATEDIFF(part, start, end). |
| DecimalToDecimalCast | Review required | Review required | Review required | Check behaviour in your runtime.Context-dependent |
| DecimalToDFloatCast Also: DFloatToDecimal | Review required | CAST(a AS FLOAT) | Review required | Check behaviour in your runtime. |
| DivMath | Review required | Review required | Review required | Check behaviour in your runtime. |
| DownCaseString Also: Lower, Tolower | LOWER(…) | LOWER(…) | LOWER(…) | Check behaviour in your runtime. |
| DQuoteString | CONCAT('"', a, '"') | '"' + a + '"' | '"' || a || '"' | Check behaviour in your runtime. |
| EReplaceString Also: Ereplace, Replace, Change, ReplaceFromList | REPLACE(…) | REPLACE(…) | REPLACE(…) | DataStage Change/EReplace may carry occurrence/begin args that the 3-arg REPLACE does not model. |
| ExpMath | EXP(…) | EXP(…) | EXP(…) | Check behaviour in your runtime. |
| FieldString | SPLIT(a, b)[c - 1] | (SELECT value FROM STRING_SPLIT(a, b) ) | SPLIT_PART(a, b, c) | DataStage Field() occurrence is 1-based; T-SQL STRING_SPLIT does not guarantee ordering — review the occurrence selection. |
| FloorMath | FLOOR(…) | FLOOR(…) | FLOOR(…) | Check behaviour in your runtime. |
| FmtString | FORMAT_STRING(b, a) | FORMAT(a, b) | a | DataStage Fmt() format codes do not map 1:1 to SQL format strings.Semantic differences |
| GenerateKey | SEQUENCE(1, a, 1) | a | ARRAY_GENERATE_RANGE(1, a + 1) | Check behaviour in your runtime. |
| GenerateKeyKey Also: SurrogateKeyGen, SurrogateKeyGenerator, NextSurrogateKey | Review required | Review required | Review required | DataStage surrogate-key generators keep persistent state; the window form restarts numbering each run. Add MAX(key)+1 offset for continuity across loads.Context-dependent |
| HoursFromTimeDate & Time | HOUR(a) | DATEPART(HOUR, a) | HOUR(a) | Check behaviour in your runtime. |
| IndexString | INSTR(a, b) | CHARINDEX(b, a) | CHARINDEX(b, a) | DataStage Index() supports an occurrence argument; the SQL form returns the FIRST occurrence only. Review if occurrence > 1.Semantic differences |
| IntegerToDecimalCast | Review required | Review required | Review required | Check behaviour in your runtime. |
| IntegerToStringCast Also: DecimalToString, TimestampToString, TimeToString, DfloatToStringNoExp, UstringToString | Review required | Review required | Review required | Check behaviour in your runtime.Context-dependent |
| IsNotNullNull handling | Review required | Review required | Review required | Check behaviour in your runtime. |
| IsNullNull handling Also: ISNULL | Review required | Review required | Review required | Check behaviour in your runtime. |
| IsValidDecimalCast | Review required | (TRY_CAST(a AS DECIMAL) IS NOT NULL) | Review required | Check behaviour in your runtime. |
| JulianDayFromDateDate & Time | (DATEDIFF(a, DATE '1970-01-01') + 2440588) | (DATEDIFF(DAY, '1970-01-01', a) + 2440588) | (DATEDIFF(DAY, '1970-01-01'::DATE, a) + 2440588) | Check behaviour in your runtime.Semantic differences |
| LABSMath | Review required | Review required | Review required | Check behaviour in your runtime. |
| LDEXPMath | Review required | Review required | Review required | Check behaviour in your runtime. |
| LeftString | SUBSTRING(a, 1, b) | LEFT(a, b) | LEFT(a, b) | Check behaviour in your runtime. |
| LenString Also: Length, RawLength | LENGTH(…) | LENGTH(…) | LENGTH(…) | Check behaviour in your runtime. |
| LNMath | LN(…) | LOG(a) | LN(…) | Check behaviour in your runtime. |
| Log10Math | LOG10(…) | LOG10(…) | LOG10(…) | Check behaviour in your runtime. |
| MantissaFromDecimalCast | Review required | Review required | Review required | Mantissa extraction is approximate; review precision handling.Semantic differences |
| MantissaFromDfloatCast | Review required | Review required | Review required | Check behaviour in your runtime.Semantic differences |
| MaxValueMath | GREATEST(…) | GREATEST(…) | GREATEST(…) | Check behaviour in your runtime. |
| MicroSecondsFromTimeDate & Time | UNIX_TIMESTAMP(a) * 1000000 | DATEPART(MICROSECOND, a) | DATE_PART(EPOCH_MICROSECOND, a) | Check behaviour in your runtime.Semantic differences |
| MidnightSecondsFromTimeDate & Time | (HOUR(a)*3600 + MINUTE(a)*60 + SECOND(a)) | DATEDIFF(SECOND, CAST(CAST(a AS DATE) AS DATETIME2), a) | (HOUR(a)*3600 + MINUTE(a)*60 + SECOND(a)) | Check behaviour in your runtime. |
| MinutesFromTimeDate & Time | MINUTE(a) | DATEPART(MINUTE, a) | MINUTE(a) | Check behaviour in your runtime. |
| MinValueMath | LEAST(…) | LEAST(…) | LEAST(…) | Check behaviour in your runtime. |
| ModMath | MOD(a, b) | (a % b) | MOD(a, b) | Check behaviour in your runtime. |
| MonthFromDateDate & Time | Review required | Review required | Review required | Check behaviour in your runtime. |
| ndp.TransformStringToDateDate & Time | Review required | Review required | Review required | Check behaviour in your runtime. |
| NEGMath | Review required | Review required | Review required | Check behaviour in your runtime. |
| NextWeekdayFromDateDate & Time | DATE_ADD(a, 1) | DATEADD(DAY, 1, a) | DATEADD(DAY, 1, a) | DataStage NextWeekdayFromDate(date, weekday) finds the next named weekday; this approximation advances one day — review the target weekday argument.Semantic differences |
| NullToEmptyNull handling Also: NullToString | Review required | Review required | Review required | Check behaviour in your runtime. |
| NullToValueNull handling Also: NullToValues, HandleNull | COALESCE(…) | COALESCE(…) | COALESCE(…) | Check behaviour in your runtime. |
| NullToZeroNull handling | Review required | Review required | Review required | Check behaviour in your runtime. |
| NumString | (TRY_CAST(a AS DOUBLE) IS NOT NULL) | (TRY_CAST(a AS FLOAT) IS NOT NULL) | (TRY_CAST(a AS DOUBLE) IS NOT NULL) | DataStage Num() returns 1 if the string is numeric. |
| OconvDate & Time | Review required | Review required | Review required | DataStage Oconv() conversion codes are open-ended; only @DATE/@TIME are mapped. Other codes pass through unchanged.Semantic differencesContext-dependent |
| PadStringString | RPAD(a, b, c) | a + REPLICATE(c, b) | RPAD(a, b, c) | Check behaviour in your runtime. |
| PreviousWeekdayFromDateDate & Time | DATE_SUB(a, 1) | DATEADD(DAY, -1, a) | DATEADD(DAY, -1, a) | Approximation — finds the previous named weekday in DataStage.Semantic differences |
| PwrMath Also: Power, Pow | POWER(…) | POWER(…) | POWER(…) | Check behaviour in your runtime. |
| RecCountKey Also: RowCount | Review required | Review required | Review required | Check behaviour in your runtime. |
| RecNumKey Also: RowNum, Recount | Review required | Review required | Review required | Check behaviour in your runtime. |
| RightString | SUBSTRING(a, -(b)) | RIGHT(a, b) | RIGHT(a, b) | Check behaviour in your runtime. |
| RndMath Also: Random, Rand | RAND() | RAND() | UNIFORM(0::FLOAT, 1::FLOAT, RANDOM()) | Check behaviour in your runtime. |
| RoundMath | ROUND(…) | ROUND(…) | ROUND(…) | Check behaviour in your runtime. |
| SecondsFromTimeDate & Time | SECOND(a) | DATEPART(SECOND, a) | SECOND(a) | Check behaviour in your runtime. |
| SecondsSinceFromTimestampDate & Time | (UNIX_TIMESTAMP(a) - UNIX_TIMESTAMP(b)) | DATEDIFF(SECOND, b, a) | DATEDIFF(SECOND, b, a) | Check behaviour in your runtime. |
| SeqString Also: Ascii | ASCII(…) | ASCII(…) | ASCII(…) | Check behaviour in your runtime. |
| SetNullNull handling | Review required | Review required | Review required | DataStage SetNull() yields a typed NULL — the column's display datatype should drive the CAST.Context-dependent |
| SignMath | SIGN(…) | SIGN(…) | SIGN(…) | Check behaviour in your runtime. |
| SoundexString | SOUNDEX(…) | SOUNDEX(…) | SOUNDEX(…) | Check behaviour in your runtime. |
| SqrtMath | SQRT(…) | SQRT(…) | SQRT(…) | Check behaviour in your runtime. |
| SQuoteString Also: Squote | CONCAT("'", a, "'") | '''' + a + '''' | '''' || a || '''' | Check behaviour in your runtime. |
| StrString Also: Space | REPEAT(a, b) | REPLICATE(a, b) | REPEAT(a, b) | Check behaviour in your runtime. |
| StringToDateDate & Time Also: IsValidDate | CAST(a AS DATE) | TRY_CAST(a AS DATE) | TRY_CAST(a AS DATE) | Check behaviour in your runtime. |
| StringToDecimalCast | Review required | Review required | Review required | Check behaviour in your runtime.Context-dependent |
| StringToIntegerCast | Review required | Review required | Review required | Check behaviour in your runtime. |
| StringToRawCast | Review required | CAST(a AS VARBINARY) | Review required | Check behaviour in your runtime. |
| StringToTimeDate & Time | CAST(a AS TIMESTAMP) | TRY_CAST(a AS TIME) | TRY_CAST(a AS TIME) | Check behaviour in your runtime. |
| StringToTimestampDate & Time | TO_TIMESTAMP(a) | TRY_CAST(a AS DATETIME2) | TO_TIMESTAMP(a) | Check behaviour in your runtime. |
| StringToUStringCast Also: UStringToString | Review required | Review required | Review required | Check behaviour in your runtime. |
| StripWhiteSpaceString | REGEXP_REPLACE(a, '\\s', '') | REPLACE(REPLACE(REPLACE(a, ' ', ''), CHAR(9), ''), CHAR(10), '') | REGEXP_REPLACE(a, '\\s', '') | Check behaviour in your runtime. |
| SubstringString | SUBSTRING(…) | SUBSTRING(…) | SUBSTRING(…) | Check behaviour in your runtime. |
| SubstringsString | SUBSTRING(…) | SUBSTRING(…) | SUBSTRING(…) | Check behaviour in your runtime. |
| TimeFromComponentsDate & Time | MAKE_TIMESTAMP(1970, 1, 1, a, b, c) | TIMEFROMPARTS(a, b, c, 0, 0) | TIME_FROM_PARTS(a, b, c) | Check behaviour in your runtime. |
| TimestampFromDateTimeDate & Time | TO_TIMESTAMP(CONCAT(a, ' ', b), 'yyyy-MM-dd HH:mm:ss') | CAST(CONCAT(CAST(a AS VARCHAR), ' ', CAST(b AS VARCHAR)) AS DATETIME2) | TO_TIMESTAMP(CONCAT(a, ' ', b)) | Check behaviour in your runtime. |
| TimestampFromSecondsSinceDate & Time Also: TimestampFromTimet | FROM_UNIXTIME(a, 'yyyy-MM-dd HH:mm:ss') | DATEADD(SECOND, a, '1970-01-01') | TO_TIMESTAMP(a) | Check behaviour in your runtime. |
| TimestampOffsetByComponentsDate & Time | a + INTERVAL b YEAR + INTERVAL c MONTH + INTERVAL d DAY + INTERVAL e HOUR + INTERVAL f MINUTE + INTERVAL g SECOND | DATEADD(SECOND, g, DATEADD(MINUTE, f, DATEADD(HOUR, e, DATEADD(DAY, d, DATEADD(MONTH, c, DATEADD(YEAR, b, a)))))) | DATEADD(SECOND, g, DATEADD(MINUTE, f, DATEADD(HOUR, e, DATEADD(DAY, d, DATEADD(MONTH, c, DATEADD(YEAR, b, a)))))) | Check behaviour in your runtime. |
| TimestampToDateDate & Time | CAST(a AS DATE) | CAST(a AS DATE) | CAST(a AS DATE) | Check behaviour in your runtime. |
| TimestampToTimeDate & Time | DATE_FORMAT(a, 'HH:mm:ss') | CAST(a AS TIME) | CAST(a AS TIME) | Check behaviour in your runtime. |
| TimetFromTimestampDate & Time | UNIX_TIMESTAMP(a) | DATEDIFF(SECOND, '1970-01-01', a) | DATE_PART(EPOCH_SECOND, a) | Check behaviour in your runtime. |
| TrimString Also: TrimLeadingTrailing, Trim Leading Trailing, string_trim | TRIM(…) | TRIM(…) | TRIM(…) | Check behaviour in your runtime. |
| TrimBString Also: TrimTrailing | RTRIM(a) | RTRIM(a) | RTRIM(a) | Check behaviour in your runtime. |
| TrimFString Also: TrimLeading | LTRIM(a) | LTRIM(a) | LTRIM(a) | Check behaviour in your runtime. |
| TruncateMath Also: Trunc | TRUNCATE(…) | ROUND(a, b, 1) | TRUNCATE(…) | Check behaviour in your runtime. |
| UpCaseString Also: Upper, Toupper | UPPER(…) | UPPER(…) | UPPER(…) | Check behaviour in your runtime. |
| UtilityAbortToLogUtil Also: force_error | RAISE_ERROR(a) | THROW 50000, a, 1 | a | Check behaviour in your runtime.Semantic differences |
| WeekdayFromDateDate & Time | DATE_FORMAT(a, 'EEEE') | DATENAME(WEEKDAY, a) | DAYNAME(a) | Check behaviour in your runtime. |
| WeekdayFromDate2Date & Time | DAYOFWEEK(a) | DATEPART(WEEKDAY, a) | DAYOFWEEK(a) | Check behaviour in your runtime. |
| YeardayFromDateDate & Time | DAYOFYEAR(a) | DATEPART(DAYOFYEAR, a) | DAYOFYEAR(a) | Check behaviour in your runtime. |
| YearFromDateDate & Time | Review required | Review required | Review required | Check behaviour in your runtime. |
| YearweekFromDateDate & Time | WEEKOFYEAR(a) | DATEPART(WEEK, a) | WEEKOFYEAR(a) | Check behaviour in your runtime. |
No matching functions. Try another name or choose all categories.