Thanks, that is exactly what we ended up doing, in conjunction with the NVL2 function to capture the nulls and provide a value for them.
SELECT NVL2(Submitted_Date,TO_CHAR(Submitted_Date,'dd-Mon-yyyy'),'NA') FROM ... Thanks for you help. Ian -----Original Message----- From: Dave Carabetta [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 2:11 PM To: CF-Talk Subject: RE: CF4.5 and CFMX Oracle ODBC Drivers >Thanks for the interesting comments. What is weird about this to me, is >that I'm not INSERTing the data, I'm SELECTing the date. The date is >already in the Oracle database table. It was put into it by another tool >which assumable did so correctly, the data is there. But when I try to >SELECT the date field it sometimes throws an error. What is even weirder, >it only throws the error for some records and not others. And apparently >the offending records can be selected, if good records are selected first! >This is just bizarre to me. > >Thanks for your insights. I didn't see my reply come through in the arhives to this, so here goes one more time. Sorry if you already got this. For selecting data, I use Oracle's to_char() function to convert the date to a formatted string. This gives me the added flexibility of formatting my date in the query as opposed to using DateFormat() in CF, and I only have to go to one place to change the date mask if I need to. Here's an example: SELECT to_char(tablename.dateColumn, 'MM/DD/YYYY') AS formattedDate FROM tablename (Note that the "AS" keyword in Oracle isn't strictly necessary, but I do it for clarity and because I'm used to it.) Again, the mask that is applied can vary depending on what you want. See the Oracle docs for the multitude of options. As I mentioned in my previous post, using this approach has eliminated any of the date insert/update/select issues I used to see in CF 4.5, 5, and MX. Hope this helps, Dave. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
