• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Oracle 10g - Date format in SELECT query for CFOUTPUT

LEGEND ,
Jan 28, 2013 Jan 28, 2013

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,

^_^

Views

2.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Jan 28, 2013 Jan 28, 2013

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.

Votes

Translate

Translate
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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.

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

Copy link to clipboard

Copied

How is it erroring without there being an error message?

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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.

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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.

^_^

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2013 Jan 28, 2013

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 29, 2013 Jan 29, 2013

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation