Display time prtion of Oracle 8.1.5 date field with Coldfusion

[minyme]

Hi,

I have a date field in one of the tables of Oracle 8.1.5. (The SYSDATE from Oracle is inserted into this date field.) I have already checked if this date field contains the time portion and it does. Then, I want to display the time portion of it on the screen with Coldfusion. I've used the TimeFormat(DateField, "HH:MM:SS&quotfunction. Sometimes it displays the right time and sometime it displays 00:00:00. Could someone help me with this?
Thank you,

 

[GunJack]

Although I doubt this is it, try "HH:mm:ss" as the mask is case sensitive and I don't show any listed behavior for "MM" or "SS". If this isn't it, I would output the un-formatted value of "DateField" and see if you see anything different between the records that display correctly and those that don't.

GJ

 

[pjbarteck]

minyme,
The best way to extract time for a date field input by sysDate is a little cumbersome but that is because of the way it is stored.
Try this:

<cfquery name=&quot;gettime&quot; datasource=&quot;databasename&quot;>
select timefieldname from table
where variable_a = value
</cfquery>
<cfset timehour = hour(#gettime.timefieldname#)>
<cfset timeminute = minute(#gettime.timefieldname#)>
<cfset timesecond = second(#gettime.timefieldname#)>
<cfset combinedtime = '#timehour#' &amp; &quot;:&quot; &amp; '#timeminute#' &amp; &quot;:&quot; &amp; '#timesecond#'>
<cfoutput>
time = #combinedtime#
</cfoutput>


That should do it for you. Masks generally don't work to pull the time only.

If you don't mind getting the date and time you can go direct to it by:
///after query to get value in above example/////
<cfoutput>
date/time = #gettime.timefieldname#
</cfoutput>

Hope this helps,
pjbarteck

 

[Jami]

I am also using Oracle and to store and retrieve date and time information, I just use the to_date (to save to Oracle) and to_char (to retrieve from Oracle) functions. You can use these as follows:

<cfquery>
insert into yourtable (datefield)
values (TO_DATE('#formfield#', 'MM/DD/YYYY HH24:MI')
</cfquery>

To recall:

<cfquery>
select to_char(w.deadline, 'MM/DD/YYYY HH24:MI')
from yourtable
where ...
</cfquery>

If you only want the time, then use a different format like
'HH:MI'. I used military time format and date above. There are many different formatting options to help get at what you want. Hope that helps!

Also important: Oracle uses 'MM' to denote months, so be sure to use 'MI' to specify minutes with these functions.

Jami

 

[Jami]

Oops... I missed a closing parenthesis on the insert statement. Thought I'd mention it in case it gave you any trouble,
Jami