Skip to main content
I
IQ1
Inspiring
May 16, 2019
Question

Datetime format ?

  • May 16, 2019
  • 5 replies
  • 2286 views

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

    This topic has been closed for replies.

    5 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 17, 2019

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

    I
    IQ1Author
    Inspiring
    May 17, 2019

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

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 17, 2019

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 17, 2019

    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'

    I
    IQ1Author
    Inspiring
    May 17, 2019

    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
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    May 17, 2019

    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)
    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 17, 2019

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

    WolfShade
    WolfShade
    Legend
    May 16, 2019

    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,

    ^ _ ^

    EddieLotter
    EddieLotter
    Inspiring
    May 16, 2019

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices