3

I am in the process of upgrading from CF 9 to CF 2016, and seeing some odd behavior in the data returned from a StoredProc call (cfscript).

The data type in the database is time(7). The database value I'm seeing the issue with is: 00:00:00.0000000.

I am dumping the results directly after the execute().getProcResultSets().results line.

  • In CF 9, the value in the cfdump is 00:00:00.0000000 which is correct.

  • In CF 2016, the value in the cfdump is 1970-01-01 00:00:00.0

This difference is causing an "invalid datetime error" in my code: 

CreateODBCDateTime("#end_date# #end_time#")

I fixed this by using a TimeFormat() function call, but I'd like to know if there is another option for fixing this so the data returned by CF's StoredProc is correct, to eliminate any possible confusion.

UPDATE: I am using the default driver provided by Adobe.

SOS
  • 6,430
  • 2
  • 11
  • 29
wellercs
  • 143
  • 5
  • 2
    Ah yes, the ever popular software upgrade breaks my code problem. Happens to me all the time. In any event, what cf2016 is giving you is a more appropriate representation of what a time(7) datatype really is. – Dan Bracuk Jan 11 '18 at 23:02
  • @DanBracuk Thanks for the reply, but I disagree. Running the stored procedure directly in SQL Server 2014 gives me the 00:00:00.0000000 value, whereas CF (in the driver presumably) is mutating it. I'm expecting CF to return the data from StoredProc() exactly how the data is returned when the proc is called directly i.e., from SQL Server Management Studio. Granted, SSMS could be formatting it too, but time(7) from my understanding should just be the hours:minutes:seconds with the milliseconds precision in this case being 7. – wellercs Jan 11 '18 at 23:15
  • What JDBC driver do you use? In both cases the one Adobe provides? – Bernhard Döbler Jan 11 '18 at 23:18
  • @BernhardDöbler Yes, it's whatever the Adobe default driver is – wellercs Jan 11 '18 at 23:35
  • 2
    Just remember that SSMS is client software that displays data in such a way to be readable. Just because you don't see the date part doesn't mean it's not there. – Dan Bracuk Jan 12 '18 at 03:41
  • 1
    Like Dan said, don't put too much stock in client-centric output, but ... seems to be due to a driver difference. In CF9.0.2, the default driver returns "time" as a String. In CF 2016 it returns a java.sql.Timestamp, which is probably more appropriate .. but shouldn't make any difference to most date functions. `"This is causing an invalid datetime error in my code" ` What code is throwing an error? What's the exact error? – SOS Jan 12 '18 at 04:16
  • "CF 2016 it returns a java.sql.Timestamp..." Which is displayed differently when dumped as a string by cfdump. – SOS Jan 12 '18 at 04:28
  • @Ageax The offending code was: CreateODBCDateTime("#end_date# #end_time#") so the CreateODBCDateTime was failing because end_time was being returned as 1970-01-01 00:00:00.0000000; it was an easy fix to fix that part, so I was less concerned about the error than I was the fact that the data in the dump seemed incorrect. – wellercs Jan 12 '18 at 16:03
  • 1
    This is one of the reasons why I don't really like having both a date and a time datatype in SQL. If you don't need to separate them, I wouldn't store them that way. A `datetime2` type would work. Also, do you need that level of precision in a date? 7 decimals of a second is pretty tiny and not very useful in much outside of scientific measurements. – Shawn Jan 12 '18 at 16:21
  • @wellercs - Oh.. yeah. The new drivers return the same time as before, but using a different data type, so it *looks* different when converted to a "string". It was convenient that CF9 returned the exact format needed, but code shouldn't depend on the raw string format of dates and times, for exactly this reason. It's better to work with date objects and functions, which are less brittle and more resistant to driver changes. – SOS Jan 12 '18 at 16:55
  • Like Shawn, I'm not a fan of separating date and time either, but if needed, use date functions to combine the two, such as DateAdd(). If you absolutely must use strings, try [ParseDateTime()](https://helpx.adobe.com/coldfusion/cfml-reference/coldfusion-functions/functions-c-d/DateTimeFormat.html) which now supports date masks. – SOS Jan 12 '18 at 17:03
  • @wellercs, when you say, `I was less concerned about the error than I was the fact that the data in the dump seemed incorrect.`, are you referring to the inclusion of the date, or the fact that all the numbers for the time part are zero? – Dan Bracuk Jan 12 '18 at 17:19
  • @DanBracuk I was referring to the inclusion of the date – wellercs Jan 12 '18 at 22:57
  • @Ageax & Shawn: I agree with what you both are saying. This is older than dirt code. I am using DateFormat() and TimeFormat() now to fix this error but I have a feeling this will just be the start of changes like that. I agree dates and times as strings are brittle, better ways to handle them, etc. – wellercs Jan 12 '18 at 23:02

0 Answers0