We help IT Professionals succeed at work.

Convert decimal to datetime

fantamen
fantamen used Ask the Experts™
on
I've to create a table adapter quering tables in an AS400 database using an odbc connection. All works fine but I want to convert a field in decimal format (for example 20120415 ... today date) in a date format (15/04/2012). I've tried cast and convert funtion but nothing.
Is there any way to convert?



Thank you
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
sure
try something like

select date( char(decimal_column/10000) || '.' || char ((decimal_column/100)%100) || '.' || char(decimal_column %100))
from your_table

or something like that

Author

Commented:
tHANK YOU FOR YOUR replay momi_sabag
|| is not recognaized so I've used a series of CONCAT functions.
Theo KouwenhovenApplication Consultant

Commented:
SELECT to_date(char(MyDecDta), 'yyyymmdd') FROM MyTable

Regards,
murph

Author

Commented:
Dear murphey2
I've used your command. This is the error returned: Error [42S02] IBM system iAccess ODBC driver DB2 for i5 TO_DATE in *LIBL di tipo *N non trovato.
i think i deserve the points
if the only change that was made is changing || (concatenate operator) to the concat function, then my solution is the correct one
the reason the || did not work probably has something to do with character conversion that is done when the linked server is accessed
Theo KouwenhovenApplication Consultant

Commented:
Yes Correct : momi_sabag deserve the points,

The || is not available for NON iso characterset, some times it's  !!, ]], [[ or ¢¢.

CONCAT is the same, so give him the points.