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

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
FROM		STRGIS.TBLSTATUSDATE
</cfquery>
 
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
 
 
<body bgcolor="#000066" background="images/blue_back.gif">
<table border="0" cellpadding="0" cellspacing="0">
	<tr>
	
		<td><font color="white" face="arial" size="2"><b>The Map is current as of <cfoutput>#DateFormat(rsDateLastMod.datdate, "m/d/yy")#</cfoutput> 
 
<cfoutput>#TimeFormat(rsDateLastMod.datdate,"short")#</cfoutput></b></font></td>
	</tr>
</table>
</body>
</html>

Open in new window

apwbeAsked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
Seems Oracle is not using the conventional ODBC date that Coldfusion expects.

http://www.adobe.com/devnet/server_archive/articles/cf_best_practices_oracle.html

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


TO_CHAR
http://www.techonthenet.com/oracle/functions/to_char.php

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



0
 
SidFishesCommented:


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

http://www.justskins.com/forums/cfmx-and-oracle-10g-jdbc-no-longer-retrieves-timestampwith-date-128214.html

probably just need to specify the mask explcitly

#timeformat(rsDateLastMod.datdate,"h:mmt")#


0
 
SidFishesCommented:
or if you want the whole date/time string

#dateformat(rsDateLastMod.datdate,"dd/mm/yyyy hh:mm:ss")#
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
gdemariaCommented:
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.
0
 
apwbeAuthor Commented:
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.
date-value.jpg
0
 
gdemariaCommented:
Ok, good.  That eliminates the question of being in the database.

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


0
 
apwbeAuthor Commented:
Yes, I tried Sid's format and got basicall the same results ..12:00..I am at a real loss on this one.
0
 
apwbeAuthor Commented:
I got it to work. The final code:

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

..........................

   
      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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.