Format Oracle Date to CF Output

Thank you tmschmitt, this reply lead me to my answer. I setup the EpochTimeToDate() UDF and then applied DateFormat to my output:

<cfoutput>#DateFormat(EpochTimeToDate(mydata.ASSIGN_TIME), "MM/DD/YYYY")#</cfoutput>

So, now my output is 08/23/2007. AWESOME!!!

What you are dealing with there is UnixTime, which is the number of seconds that have passed since the Epoch, which is Jan 1st, 1970.

You can use something like this to do the conversion in oracle.

select to_timestamp_tz('1970-01-01 utc', 'yyyy-mm-dd tzr')+
numtodsinterval(1187896509,'second') dstamp
from dual;

1187896509 is GMT Thu, 23 Aug 2007 19:15:09 GMT in Unix Time (POSIX).
I work with these in SQL Server and they are a total pain. I have no idea if Oracle can work with them. I doubt it.
I have CF UDFs that convert to/from this format, as well as SQL UDFs that do the same thing. Generally for outputting from a SELECT query, I'll use the SQL UDF inline to convert the integer value to a valid date/time boject.
Search Google... You'll find plenty of info.

What do you see when you cfdump the query.column?

Assuming your goal is to 'Format Oracle Date to CF Output', then the CF
dateFormat() function is probably what you want.


<cfoutput query="myData">
#dateFormat(assign_time,"mm/dd/yyyy")#<br/>
</cfoutput>

What are you attempting to do? Display the date, select the date,
filter the date?

Your code does not give an indication of the goal.