Convert SQL Date into AS400 Date Format

Usually AS400 supports millennium date format(i.e. CYYMMDD). SQL Server’s default Date format is YYYY-MM-DD. For obtaining our output we have to convert SQL Server Date to ISO Date, then it will be converted to AS400 date format easily.

Step 1: Convert SQL date format to ISO date format.

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) 

GO 

Step 2 : Subtract 19000000 from ISO date format.

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000 

GO 

Step 3 : Convert the result to string and  then trim the spaces in the left.

SELECT LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)) 

GO 

Step 4 : Add the zero to left side of the result.

SELECT '0' + LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)) 

GO 

Step 5 : Return the 7 characters from the Right part of the result.

SELECT RIGHT('0' + LTRIM(STR(CONVERT(VARCHAR(8), GETDATE(), 112) - 19000000)),7) 

GO 

Now we can get the AS400 date format as a result.

Related Posts

1. Inbuilt Functions in Oracle
2. Oracle PL/SQL: Cursors
3. SQL Server 2008 New DATETIME Data Types
4. Oracle PL/SQL: Loops and Conditional Statements
5. Oracle PL/SQL: Advanced Cursors

Tags: AS400 Date, ISO Date, SQL Date, SQL Server 2008, SQL Server Date to ISO Date, SQL to AS400 Date