cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC-Adapter (Oracle): Convert decimal(19) to date

Former Member
0 Kudos

Hi everybody,

I want to use a SQL expression, where a decimal(19) which contains a date (as long).

The SQL-statement should format the decimal into a date-format.

Sample:

Field has value = 1240524000000 which is as date = 24.04.2009 10:39:00

SELECT TO_DATE('1240524000000', 'YYYYMMDD') FROM myTABLE

does not work.

I know that this is XI/PI forum. But maybe somebody has experience with this.

Please note: I do not want to convert in Mapping! I want to convert via the JDBC-Adapter

Thanks, regards

Mario

Edited by: Mario Müller on Apr 27, 2009 9:33 AM

Accepted Solutions (0)

Answers (3)

Answers (3)

prasannakrishna_mynam
Contributor
0 Kudos

Hi Mario,

SELECT statement must have exactly one <access> element, try with the below statement.

SELECT TO_DATE('1240524000000', 'YYYYMMDD') as col1 FROM myTABLE

and If there is no <key> element, or if there is a <key> element but it is empty, it might cause problem check with this.

Regards,

Prasanna

Former Member
0 Kudos

The solution is:

SELECT TO_DATE('01-jan-1970','dd-mon-yyyy') + TIMEMILLISCOLUMN/(1000*60*60*24) FROM MYTABLE

Regards Mario

Former Member
0 Kudos

[http://www.mcs.csueastbay.edu/support/oracle/doc/10.2/server.102/b14200/functions183.htm]

see the example here

former_member181962
Active Contributor
0 Kudos

HI Mario,

Just googled it in the web.

see if this is relevant to you.

http://bytes.com/groups/ibm-db2/465990-convert-decimal-date

Regards,

Ravi