Functions to transform data

One of the most common and useful functions that can be used within a transfer is the DATE function. Common practice is to store a data on the AS/400 as either a numeric field of 6 digits, or perhaps even three fields of 2 digits each. While users may know these digits represent a date, they are stored not as data values but numeric values. The SQL Server and many query and development tools on the other hand have data types for date storage and manipulation. If the data is not stored as a date data type, the date editing and manipulation may not function.

A transfer using the DATE function might be:

transfer to 'mysqlsvr sa *';
select invid,DATE(invdate, YYMMDD),invamt from invoices;
bulk insert into voinces;
go

Similarly, the SUBSTRING, STR, MIN, MAX and many more Transact SQL statements are accepted, translated into AS/400 SQL and executed.