extracting data and converting AS400 date format
hi, this question is very simple I should guess.. I am trying to extract data from AS400 into excel, and since date format is CYMD, I want it to show in a MM/DD/YYYY format. Please show me how.. Thank you very much!
p.s.
in SQL please
Software/Hardware used:
Software/Hardware used:
ASKED:
February 11, 2010 2:49 AM
UPDATED:
February 12, 2010 8:19 AM
Answer Wiki:
SELECT
days(date(substr(digits(field),5,2) || '/'
|| substr(digits(field),7,2) || '/'
|| substr(digits(field),3,2))) -
days(date('01/01/1900')) + 1 as excel_serial_date_number
FROM file1
Excel stores dates as a serial number (1 = 1/1/1900, 2 = 1/2/1900, ...)
Unfortunately when you bring this in, you then need to format the column in Excel as date to see it.
____________________________________________________________
...unless you cast it as a CHAR field first.
To see all answers submitted to the Answer Wiki: View Answer History.
geee.. thanks a lot! it sure is a great help!ΓΌ