This content has been marked as final. Show 9 replies
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.
Assuming your goal is to 'Format Oracle Date to CF Output', then the CF
dateFormat() function is probably what you want.
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.
What do you see when you cfdump the query.column?
Cfdump gives me the following (without dateFormat function):
> Cfdump gives me the following (without dateFormat function):
I don't think you have a date field there, I'm not sure what you have.
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 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')+
Thank you tmschmitt, this reply lead me to my answer. I setup the EpochTimeToDate() UDF and then applied DateFormat to my output:
So, now my output is 08/23/2007. AWESOME!!!