Datetime format ?

[IQ1]

Hi All,

   ColdFusion 2018 is passing a datetime in the format '2019-05-16 23:59:00.0'

The code I am using is given below

<cftry>

<cfset fromDateObject=parsedateTime(datefrom)>

<cfset toDateObject=parsedateTime(dateto)>

<cfset fromDateTimeString=dateFormat(fromDateObject, "yyyy/mm/dd") & " " & timeFormat(fromDateObject, "HH:nn:ss")>

<cfset toDateTimeString=dateFormat(toDateObject, "yyyy/mm/dd") & " " & timeFormat(toDateObject, "HH:nn:ss")>

  <cfstoredproc procedure="dbo.multi_report_pkg.run_multi_report" datasource="#datasource#" username="#Session.UserName#" password="#Session.password#">

    <cfprocparam  value="#Session.multi_rep_id#">

    <cfprocparam  value="#Session.UserName#">

    <cfprocparam  value="#multi_report_def_id#">

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#parsedateTime(fromDateTimeString)#">

    <cfprocparam cfsqltype="CF_SQL_TIMESTAMP" value="#parsedateTime(toDateTimeString)#">

    <cfprocparam  value="#period#">

  </cfstoredproc>

<cfcatch type="any">

  <cfdump var="#cfcatch#" label="cfcatch" >

  <cfdump var="#variables#" label="variables">

   <cfdump var="#session#" label="session">

</cfcatch>

</cftry>

in the backend in Oracle , If I use to_date its not working and I get an error message

"ORA-01830: date format picture ends before converting entire input string

01830. 00000 -  "date format picture ends before converting entire input string"

*Cause: 

*Action:

Would like to know what should I use in this case, to make ColdFusion 2018 pass the date in the format yyyy-mm-dd hh24:mi:ss  ?

Thanks

IQ

[EddieLotter]

IQ1  wrote

in the backend in Oracle , If I use to_date its not working

Why are you doing that? From what you show, the date fields are already in date format.

[WolfShade]

Remove parseDateTime from the cfprocparam value.  I do believe it isn't needed.

BTW.. you can use DateTimeFormat instead of separate DateFormat and TimeFormat functions.  The caveat being that minutes are "nn", not "mm" because "mm" is month.

HTH,

^ _ ^

[BKBK]

I am confused by the following:

IQ1  wrote

   ColdFusion 2018 is passing a datetime in the format '2019-05-16 23:59:00.0'

...

Would like to know what should I use in this case, to make ColdFusion 2018 pass the date in the format yyyy-mm-dd hh24:mi:ss 

In any case, if the result is in the format yyyy/mm/dd HH:nn:ss - which I would expect - but you wish to have yyyy-mm-dd HH:nn:ss instead, then use:

<cfset fromDateTimeString=dateFormat(fromDateObject, "yyyy-mm-dd") & " " & timeFormat(fromDateObject, "HH:nn:ss")>

<cfset toDateTimeString=dateFormat(toDateObject, "yyyy-mm-dd") & " " & timeFormat(toDateObject, "HH:nn:ss")>

or, following WolfShade's suggestion,

<cfset fromDateTimeString=datetimeFormat(fromDateObject, "yyyy-mm-dd HH:nn:ss")>

<cfset toDateTimeString=dateTimeFormat(toDateObject, "yyyy-mm-dd HH:nn:ss")>

[BKBK]

IQ1  wrote

in the backend in Oracle , If I use to_date its not working and I get an error message

"ORA-01830: date format picture ends before converting entire input string

01830. 00000 -  "date format picture ends before converting entire input string"

*Cause: 

*Action:

Could it be because you are passing the wrong mask to the to_date function? Use

     TO_DATE(datetimeString,'YYYY-MM-DD HH24:MI:SS') when datetimeString has the format 'YYYY-MM-DD HH24:MI:SS'

and

     TO_DATE(datetimeString,'YYYY/MM/DD HH24:MI:SS') when datetimeString has the format 'YYYY/MM/DD HH24:MI:SS'

[IQ1]

For some reason the string that goes to Oracle from ColdFusion is of the format '2019-05-17 12:22:12.0'

Notice the .0 at the end due to this it fails.

[Charlie Arehart]

You would help yourself and us by creating a simple few-line template that demonstratea the problem, with no dependencies from your end (all static code, no use of sql, etc.) It will help you prove the problem, then help us assess what may be amiss. /CharlieSent via the Samsung Galaxy S7 active, an AT&T 4G LTE smartphone

[WolfShade]

Are you still using parseDateTime?  As I understand it, it returns a format of

{ts '2019-05-17 08:53:33'}

Which makes me wonder if Oracle is working with that, or if Oracle needs something different.

HTH,

^ _ ^

[BKBK]

IQ1  wrote

For some reason the string that goes to Oracle from ColdFusion is of the format '2019-05-17 12:22:12.0'

Notice the .0 at the end due to this it fails.

That's a surprise. Like WolfShade​, I expected no decimals.

[EddieLotter]

Please answer my question. It appears to have been lost in the flurry of responses.

I'm guessing you need to either pass the parameter as a varchar and then use the to_date function in Oracle to convert it, or you need to pass the parameter as a timestamp and not use the to_date function in Oracle.

[IQ1]

Attached is the screenshot, for now I am leaving this issue as it is leading nowhere, I seriously think there is a problem with ColdFusion's JDBC drivers. Thanks guys for all your help.

[BKBK]

IQ1  wrote

Attached is the screenshot, for now I am leaving this issue as it is leading nowhere, I seriously think there is a problem with ColdFusion's JDBC drivers. Thanks guys for all your help.

IQ1​, you're right. There is actually a decimal in the query parameter!

Try then EddieLotter 's suggestion: "pass the parameter as a varchar and then use the to_date function in Oracle to convert it". That is,

<cfprocparam cfsqltype="VARCHAR" value="#fromDateTimeString#">

<cfprocparam cfsqltype="VARCHAR" value="#toDateTimeString#">

Another alternative to try: use the current ColdFusion code, but do something like this at the Oracle end:

to_date(to_char(dateArg,'YYY-MM-DD HH24:MI:SS'), 'YYY-MM-DD HH24:MI:SS')

[BKBK]

Hi IQ1​

Please consider looking at this one last time. ColdFusion owes you an apology. Apparently, the problem is not with your code or with the database driver.

The problem is with the query parameter attribute cfsqltype="CF_SQL_TIMESTAMP". It converts the input into an object of type java.sql.timestamp. In so doing, it includes the fractional (decimal) number of seconds, the so-called nanos.

Yet another reason to use

<cfprocparam cfsqltype="VARCHAR" value="#fromDateTimeString#">

<cfprocparam cfsqltype="VARCHAR" value="#toDateTimeString#">

[IQ1]

Thanks BKBK, timestamp usage was suggested as a solution to solve the problem, originally it was VARCHAR. So VARCHAR does not work, I have tried this now and several times in the past.

[EddieLotter]

Please confirm that you are NOT using to_date in Oracle when you pass the parameter as a timestamp type.

[BKBK]

In the heat of the action, I mistakenly posted a suggestion in your previous thread.

[BKBK]

Just remembered one more thing. What is the update level of your ColdFusion 2018 installation? The updates contain fixes and extensions relevant to your question about Oracle and datetime. Note, in particular, that there used to be an issue with the function datetimeFormat().

[IQ1]

I have tried all suggestions mentioned above, thanks guys for your help.

The latest patch is updated, the issue is still not resolved.

[BKBK]

Hi IQ1​, as the issue is still unresolved, please unmark as my last post as the correct answer.