Copy link to clipboard
Copied
Hello, everyone.
The project that I am working on in the dev environment is connected to an Oracle 11g database, but the production side is Oracle 10g.
I have a page that is erroring in production (but not development) when it gets to a date that it needs to display.
A co-worker mentioned that, in 10g, if a date/time is being SELECTed, you have to put it in to_char(), so I did that.
But it's still erroring, and I'm not getting an error message, so I'm assuming that I have an incorrect format for the date in the SELECT.
What is the proper format for SELECTing a date/time when using to_char()? Right now, I have SELECT to_char(create_date,'MM-DD-YYYY HH:MI') FROM tableA . Is this not correct for CF to output?
Thank you,
^_^
One afternoon, run SELECT to_char(sysdate,'YYYY/MM/DD HH:MI') from dual. Then decide if you picked the appropriate mask for the hours.
Copy link to clipboard
Copied
Your coworker is wrong. You don't need to_char. You can select the field and use coldfusion dateformat to display it in the format you want.
Copy link to clipboard
Copied
Does it make a difference if the datatype is TIMESTAMP instead of DATE? (I assumed it was DATE; it's not.) I am having a display issue in CF when just SELECTing the date raw, without formatting it. No error messages, it just stops processing when it gets to the date.
Co-worker indicated that the JDBC connector in CF9 strips the time off the datetime.
^_^
Copy link to clipboard
Copied
How is it erroring without there being an error message?
--
Adam
Copy link to clipboard
Copied
I don't know. All I know is that CF server stops processing when it reaches the date. It gets to the DIV where the date is supposed to be ("<div id="aboutDate">") and goes no further. No closing /div, or anything else.
The webmaster, here, might have some custom error thing in place.
^_^
Copy link to clipboard
Copied
Right. So that's fairly significant information isn't it? The problem doesn't occur when you run the query, it's when you output it.
What do you see when you dump the query out?
How are you trying to output it? You're not giving the date expression a column alias, so what are you referring to it as in your code?
Actually... just post the relevant bits of your code.
--
Adam
Copy link to clipboard
Copied
Please see my original post ("I have a page erroring in production (but not in development) when it gets to a date that it needs to display.") Sorry if it came across as vague. It made sense, to me, when I typed it. But, then, I'm usually typing fast just to get the question out there, when I'm in a hurry.
Haven't done a CFDUMP, yet, as every time I make a change in development that needs to be tested in production, I have to notify my supervisor that there are files that need to be copied into production, which can sometimes take a while, so I try to do troubleshooting on dev side - it's a pain in the you-know-what, but that's the kind of environment I'm working in.
As it turns out, changing the format in the SELECT to_char() did the trick. If anyone else has this issue with Oracle 10g, I'm now using SELECT to_char(create_date,'YYYY/MM/DD HH:MI') FROM tableA, and now the CFOUTPUT is processing the whole page. I guess the MM-DD-YYYY threw CF into a tizzy, breaking the process?
Anyhoo, it's working, now. Thank you, Dan and Adam, for your thoughts on this.
^_^
Copy link to clipboard
Copied
One afternoon, run SELECT to_char(sysdate,'YYYY/MM/DD HH:MI') from dual. Then decide if you picked the appropriate mask for the hours.
Copy link to clipboard
Copied
I'll do that as soon as my dev system is done booting up.
^_^
UPDATE: The query displays 2013/01/29 07:48, which is what I was expecting. But, then, it's before 12 Noon, here. Thanks for pointing that out. HH24 is what it should be.