Format Oracle Date to CF Output

[cfquest]

I'm querying an Oracle db and one of the fields is a date field. I am selecting many columns from this db so have tried many different versions of either DateFormat, Oracle's TO_DATE and TO_CHAR conversions but none of them worked successfully. I think what is throwing me off is that I'm not just selecting the date field, I'm selecting many fields so I've even tried a query of query with no luck.

Any help will be greatly appreciated.

[Newsgroup_User]

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.

[cfquest]

Yes, I'm trying to select the date (in my query) and then display on the page. I did use the dateFormat function to display the date but I'm getting the following output, 10/14/3167688.

[Newsgroup_User]

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>

[Dan_Bracuk]

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

[cfquest]

Cfdump gives me the following (without dateFormat function):
1187896509

[Newsgroup_User]

cfquest wrote:
> Cfdump gives me the following (without dateFormat function):
> 1187896509

I don't think you have a date field there, I'm not sure what you have.

[tmschmitt]

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.

[12Robots]

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;

[cfquest]

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