Home » Developer & Programmer » Reports & Discoverer » Discoverer Newbie question on TO_DATE
Discoverer Newbie question on TO_DATE [message #144703] Thu, 27 October 2005 14:02 Go to next message
sbattisti
Messages: 39
Registered: June 2005
Member
I'm a Discoverer 4i newbie, trying to create a relatively simple Discoverer report, but I'm pulling Adjusted Service Date from a flex field, and it's formatted as varchar2. I'm trying to use a to_date function to convert it to a date, but I can't seem to get the syntax correct.

Can anyone help?

Thanks,

Steve

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #144759 is a reply to message #144703] Fri, 28 October 2005 02:16 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Have you checked the manuals?

MHE

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #144770 is a reply to message #144703] Fri, 28 October 2005 03:26 Go to previous messageGo to next message
orahugo
Messages: 32
Registered: October 2005
Location: England
Member
Give a few examples of the values of the varchar2 field. It may be that you're using the wrong syntax.

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #144806 is a reply to message #144703] Fri, 28 October 2005 07:56 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
The dates in the varchar2 field are formatted as dd-mmm-yyyy, for example:

25-JUN-1999

When I use the calculation I get ORA01821: date format not recognized.

I've tried a number of different syntaxes, the last of which was this:

TO_DATE(table.field, 'dd-mmm-yyyy')

I've left the nlsparam blank, since it's my understanding that it will just take the default date format based on the NLS_TERRITORY init parameter.

I can't find any detailed examples in the Discoverer documentation.

Any suggestions would be welcomed!

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #144808 is a reply to message #144806] Fri, 28 October 2005 07:59 Go to previous messageGo to next message
orahugo
Messages: 32
Registered: October 2005
Location: England
Member
Try...

TO_DATE(table.field, 'dd-mon-yyyy')

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #144810 is a reply to message #144703] Fri, 28 October 2005 08:06 Go to previous messageGo to next message
sbattisti
Messages: 39
Registered: June 2005
Member
Hah, orahugo, that's just what I ws doing while I was away.

Of course, it worked. Wink

Thanks all, I told you it was a newbie question!

Report message to a moderator

Re: Discoverer Newbie question on TO_DATE [message #156563 is a reply to message #144703] Wed, 25 January 2006 15:38 Go to previous message
sbattisti
Messages: 39
Registered: June 2005
Member
As a follow-up to this, I am struggling with another similar situation. I have a field calculated as follows:

TO_DATE(table.field, 'yyyy-mm-dd')

This does an excellent job of returning my text field in date format, as follows:

15-APR-06

Unfortunately, we really need it to return as:

15-APR-2006 (4-character year instead of 2-character)

Changing our global NLS_DATE_FORMAT is not possible, so I've been trying to add the NLS_DATE_FORMAT at the end of the calculation, like this:

TO_DATE(table.field, 'yyyy-mm-dd',NLS_DATE_FORMAT='DD-MMM-YYYY')

I've used various combinations of single and double-quotes, but I can't seem to stop it from erroring out. The documentation I have found seems to indicate this should work.

The error I receive is:

ORA12702: invalid NLS parameter string used in SQL function

Any suggestions?

Thanks!

Steve

Report message to a moderator

Previous Topic: Character mode printing with reports 10g - Its very Urgent
Next Topic: repeating report pages
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 29 20:58:53 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.