[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-04-28
8
Medium Priority
?
255 Views
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
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

0
Comment
Question by:apwbe
  • 3
  • 3
  • 2
8 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 24255095


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
 
LVL 36

Expert Comment

by:SidFishes
ID: 24255137
or if you want the whole date/time string

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

Expert Comment

by:gdemaria
ID: 24256463
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:apwbe
ID: 24256509
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
 
LVL 39

Expert Comment

by:gdemaria
ID: 24256537
Ok, good.  That eliminates the question of being in the database.

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


0
 

Author Comment

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

Accepted Solution

by:
gdemaria earned 1000 total points
ID: 24256724
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
 

Author Closing Comment

by:apwbe
ID: 31575668
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

834 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