Home » SQL & PL/SQL » SQL & PL/SQL » to_date() returns year as 0007
to_date() returns year as 0007 [message #219088] Mon, 12 February 2007 20:53 Go to next message
shrinee
Messages: 2
Registered: February 2007
Junior Member
The code to_char(to_date('10-JUN-07','DD-MON-YYYY'),'YYYYMMDD') suddenly returns 00070610. The same code had been working properly and returning 20070610 till sometime back. Can anyone explain the reason for the sudden change?

I am facing this problem on Development server while the same code still runs fine on production. Can someone pls explain the root cause for this change?


Thanks
Sreeni
Re: to_date() returns year as 0007 [message #219089 is a reply to message #219088] Mon, 12 February 2007 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
The mask count MUST match the target count.
>to_char(to_date('10-JUN-07','DD-MON-YYYY'),'YYYYMMDD')
There are 9 characters on the left side of TO_DATE & 11 on the right.
to_char(to_date('10-JUN-2007','DD-MON-YYYY'),'YYYYMMDD') will work as desired.
>Can someone pls explain the root cause for this change?
PEBKAC

[Updated on: Mon, 12 February 2007 21:13] by Moderator

Report message to a moderator

Re: to_date() returns year as 0007 [message #221024 is a reply to message #219088] Fri, 23 February 2007 04:54 Go to previous messageGo to next message
shrinee
Messages: 2
Registered: February 2007
Junior Member
Hi All,
Thanks for your replies. Perhaps more baffling is that the sql statement

SELECT TO_CHAR(TO_DATE('10-JUN-07','DD-MON-YYYY'),'YYYYMMDD') FROM DUAL

retruns 20070610 when I execute it on a different server running Oracle 9i on Unix.
How is this possible when this is techinically not correct? Isn't this supposed to return 00070610?

Need your expertise in finding an answer to this.

Cheers
Sreeni
Re: to_date() returns year as 0007 [message #221048 is a reply to message #221024] Fri, 23 February 2007 07:57 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Look into NLS_DATE_FORMAT setting among other things.
Re: to_date() returns year as 0007 [message #221087 is a reply to message #219088] Fri, 23 February 2007 14:11 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
use a different mask and it will always work.

SELECT TO_CHAR(TO_DATE('10-JUN-07','DD-MON-RRRR'),'YYYYMMDD') FROM DUAL

  1* SELECT TO_CHAR(TO_DATE('10-JUN-07','DD-MON-YYYY'),'YYYYMMDD') From dual
whb@xe>/

TO_CHAR(
--------
00070610

whb@xe>edit
Wrote file afiedt.buf

  1* SELECT TO_CHAR(TO_DATE('10-JUN-07','DD-MON-RRRR'),'YYYYMMDD') From dual
whb@xe>/

TO_CHAR(
--------
20070610

whb@xe>edit
Wrote file afiedt.buf

  1* SELECT TO_CHAR(TO_DATE('10-JUN-2137','DD-MON-RRRR'),'YYYYMMDD') From dual
whb@xe>/

TO_CHAR(
--------
21370610
Previous Topic: cursor inside trigger
Next Topic: Explain plan changes drastically using CTAS....
Goto Forum:
  


Current Time: Sun Dec 11 08:00:37 CST 2016

Total time taken to generate the page: 0.06096 seconds