Oracle's timestamp field is being damaged by Coldfusion

This was a shocker but it is totally reproducable. We have Oracle 10g and Coldfusion

Which version of CF?  Have you tested this on the JDBC drivers that ship with CF10?

--

Adam

Schworak wrote: This was a shocker but it is totally reproducable. We have Oracle 10g and Coldfusion <cfquery name="getTS" datasource="oracle_dsn">     select to_timestamp('12-APR-12 11.41.17.012000000 AM') as ts from dual </cfquery> <cfoutput>#getTS.ts#</cfoutput> You will find that the output doesn't match the timestamp. If you run the SQL directly against Oracle there will be a zero just after the decimal point just like it is in the query. But when it gets displayed to the screen or accessed in any way by Coldfusion, the zero is stripped out. The code above produces this result: Oracle: 12-APR-12 11.41.17.012000000 AM Coldfusion: 2012-04-12 11:41:17.12 Of course, the trailing zeros in CF are not the issue. the fact that CF has dropped the zero after the decimal point is a very serious issue. I have tried this with many timestamps and any time a zero is after the decimal point, Coldfusion damages the data. It doesn't matter how many zeros are after the decimal point. All of them are stripped until the first non-zero digit is reached. Here is another example: <cfquery name="getTS" datasource="cf_oracle_crossdb_ru_dev">     select         to_timestamp('12-APR-12 11.41.17.9 AM') as ts1,         to_timestamp('12-APR-12 11.41.17.09 AM') as ts2,         to_timestamp('12-APR-12 11.41.17.009 AM') as ts3,         to_timestamp('12-APR-12 11.41.17.0009 AM') as ts4,         to_timestamp('12-APR-12 11.41.17.00009 AM') as ts5,         to_timestamp('12-APR-12 11.41.17.000009 AM') as ts6     from dual </cfquery> <cfoutput>     #getTS.ts1#<br>     #getTS.ts2#<br>     #getTS.ts3#<br>     #getTS.ts4#<br>     #getTS.ts5#<br>     #getTS.ts6#<br> </cfoutput> Coldfusion kicks out this... 2012-04-12 11:41:17.9 2012-04-12 11:41:17.9 2012-04-12 11:41:17.9 2012-04-12 11:41:17.9 2012-04-12 11:41:17.9 2012-04-12 11:41:17.9 OH MY! Any thoughts?

Possibly a bug! You should report it.

As a work around I have turned all the TIMESTAMP fiels into strings using TO_CHAR on the Oracle side. This fixes my issue but it pretty much means that working with timestamps that come out of Oracle is very dangerous if you count on the data being correct and actually working as if it was a a real timestamp.

Revised code:

<cfquery name="getTS" datasource="cf_oracle_crossdb_ru_dev">

    select

        to_char(to_timestamp('12-APR-12 11.41.17.9 AM')) as ts1,

        to_char(to_timestamp('12-APR-12 11.41.17.09 AM')) as ts2,

        to_char(to_timestamp('12-APR-12 11.41.17.009 AM')) as ts3,

        to_char(to_timestamp('12-APR-12 11.41.17.0009 AM')) as ts4,

        to_char(to_timestamp('12-APR-12 11.41.17.00009 AM')) as ts5,

        to_char(to_timestamp('12-APR-12 11.41.17.000009 AM')) as ts6

    from dual

</cfquery>

<cfoutput>

    #getTS.ts1#<br>

    #getTS.ts2#<br>

    #getTS.ts3#<br>

    #getTS.ts4#<br>

    #getTS.ts5#<br>

    #getTS.ts6#<br>

</cfoutput>

Coldfusion kicks out:

12-APR-12 11.41.17.900000000 AM

12-APR-12 11.41.17.090000000 AM

12-APR-12 11.41.17.009000000 AM

12-APR-12 11.41.17.000900000 AM

12-APR-12 11.41.17.000090000 AM

12-APR-12 11.41.17.000009000 AM