Skip to main content
WolfShade
WolfShade
Legend
January 28, 2013
Answered

Oracle 10g - Date format in SELECT query for CFOUTPUT

  • January 28, 2013
  • 2 replies
  • 3012 views

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,

^_^

This topic has been closed for replies.
Correct answer Dan_Bracuk

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.

^_^


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.

2 replies

A
Adam Cameron.
Inspiring
January 28, 2013

How is it erroring without there being an error message?

--

Adam

WolfShade
WolfShadeAuthor
Legend
January 28, 2013

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.

^_^

A
Adam Cameron.
Inspiring
January 28, 2013

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

D
Dan_Bracuk
Inspiring
January 28, 2013

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.

WolfShade
WolfShadeAuthor
Legend
January 28, 2013

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.

^_^

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices