• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Datetime format ?

Explorer ,
May 16, 2019 May 16, 2019

Copy link to clipboard

Copied

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

Views

1.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 16, 2019 May 16, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 16, 2019 May 16, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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")>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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'

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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')

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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#">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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().

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 17, 2019 May 17, 2019

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation