How do I display time in Coldfusion 8 from Oracle 10g

Posted on 2009-04-28
Last Modified: 2013-12-24
We have an Oracle 10g database that has a table that stores a date. The date feild is populated from a web app that is run on ColdFusion 6 MX and then retreived and displayed on another web page that is run on ColdFusion 8. For some reason the time displayed is defaulting to 12:00 AM. The date displays correctly but not the time. The applications running on ColdFusion 6 MX do not have this problem and the time will display correctly i.e. 11:55 PM. Any ideas on how to make this work without changing the database and front end application? Here is the display code I am using. I have tried a bunch of formats but still only get 12:00 AM for the time.
<cfquery name="rsDateLastMod" datasource="plowout">

SELECT	datDate






<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">


<body bgcolor="#000066" background="images/blue_back.gif">

<table border="0" cellpadding="0" cellspacing="0">



		<td><font color="white" face="arial" size="2"><b>The Map is current as of <cfoutput>#DateFormat(rsDateLastMod.datdate, "m/d/yy")#</cfoutput> 






Open in new window

Question by:apwbe
    LVL 36

    Expert Comment


    there are some issues with 10g & jdbc (but I don't think that's the issue)

    probably just need to specify the mask explcitly


    LVL 36

    Expert Comment

    or if you want the whole date/time string

    #dateformat(rsDateLastMod.datdate,"dd/mm/yyyy hh:mm:ss")#
    LVL 39

    Expert Comment

    You should also verify that the full date and time are actually in the database.   You can do this by querying the data in SQL*Plus.  Sometimes the problem is getting the full date/time into the database... instead of getting it out.

    Author Comment

    Attached is a screen shot of the data field and value. I would be real happy if I could just display the information like this.
    LVL 39

    Expert Comment

    Ok, good.  That eliminates the question of being in the database.

    Did you try Sid's suggested format?    #timeformat(rsDateLastMod.datdate,"h:mm")#


    Author Comment

    Yes, I tried Sid's format and got basicall the same results ..12:00..I am at a real loss on this one.
    LVL 39

    Accepted Solution

    Seems Oracle is not using the conventional ODBC date that Coldfusion expects.

    The work-around is to specify the date in a TO_CHAR() function in the SELECT statement to convert it to the string when fetching...


    select to_char(sDateLastMod.datdate, 'HH:MI')  as theTime


    Author Closing Comment

    I got it to work. The final code:

    SELECT to_char(datdate, 'Day DD Month YYYY HH24:MI:SS') as theTime


          The Map is current as of  #rsDateLastMod.theTime#

    This displays:
    "The Map is current as of Tuesday 28 April 2009 13:48:01"
    Thanks for the help and good luck.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now