Home » SQL & PL/SQL » SQL & PL/SQL » Trying to get info from Date field
Trying to get info from Date field [message #250640] Tue, 10 July 2007 13:31 Go to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I am trying run the following query below, but need help with the to_date part. Please advise. This query is not working.

select * from apps.pa_cost_distribution_lines_all
where pa_period_name = TO_DATE('JUL-07','MON-YY')


Anne
Re: Trying to get info from Date field [message #250642 is a reply to message #250640] Tue, 10 July 2007 13:35 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What error are you getting ?

Are you sure pa_period_name is a date field ?
Re: Trying to get info from Date field [message #250643 is a reply to message #250640] Tue, 10 July 2007 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you mean "This query is not working"?

Regards
Michel
Re: Trying to get info from Date field [message #250646 is a reply to message #250640] Tue, 10 July 2007 13:44 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I'll take one of my mind-reading guesses (I was right once before).

If your column is a DATE datatype, do realize that your query will return only rows with July 1, 2007 midnight as the value. Any other day of the month or if there is a time component with the date will not be returned.
Re: Trying to get info from Date field [message #250648 is a reply to message #250640] Tue, 10 July 2007 13:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
The amazing Kreskin strikes again Laughing
Re: Trying to get info from Date field [message #250651 is a reply to message #250640] Tue, 10 July 2007 14:03 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
I get the following error message:

ORA-01858: a non-numeric character was found where a numeric was expected

The column is as such in the table:JUL-07.

aNNE
Re: Trying to get info from Date field [message #250652 is a reply to message #250651] Tue, 10 July 2007 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is "pa_period_name" a date?
Post the result of
select TO_DATE('JUL-07','MON-YY')  from dual;

Regards
Michel
Re: Trying to get info from Date field [message #250654 is a reply to message #250651] Tue, 10 July 2007 14:08 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
pa_period_name is a varchar2(15) type of column.
So does that mean I would have to use a to_char type??
Anne
Re: Trying to get info from Date field [message #250655 is a reply to message #250640] Tue, 10 July 2007 14:10 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
if the pa_period_name is text of the form JAN-07, then why even use to_date? simply


select * from apps.pa_cost_distribution_lines_all
where pa_period_name = 'JUL-07';
Re: Trying to get info from Date field [message #250656 is a reply to message #250654] Tue, 10 July 2007 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That's the error.
Oracle try to translate it to a date as the second part of the equality is a date.
It uses your default date format which not matches with your data.
So translate, the to_date with to_char to your data format.

Regards
Michel
Re: Trying to get info from Date field [message #250664 is a reply to message #250640] Tue, 10 July 2007 14:36 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Can I try and play Kreskin ?

I'm guessing it's going to be a date variable coming into this query and not something hard coded.

So if that's the case you can try something like
select * from apps.pa_cost_distribution_lines_all
where pa_period_name = to_char(:date_field,'MON-DD')
Re: Trying to get info from Date field [message #250671 is a reply to message #250664] Tue, 10 July 2007 15:09 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
MarcL wrote on Tue, 10 July 2007 15:36
Can I try and play Kreskin ?
[/code]


Be careful, I failed in my attempt. Now I'd have to go with Bill B's answer. I think it is that simple.
Re: Trying to get info from Date field [message #250672 is a reply to message #250671] Tue, 10 July 2007 15:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is... if the date inside the varchar2 column has this format but in this case why a varchar2(15)?

Regards
Michel
Re: Trying to get info from Date field [message #250710 is a reply to message #250672] Wed, 11 July 2007 00:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It's probably not that simple. My crystal ball says the contents are a string representation of the date.
So, the query should look something like
select * from apps.pa_cost_distribution_lines_all
where to_char(to_date(pa_period_name, 'dd-mon-yyyy'), 'MON-RR') = :input_field
                                         ^
only here's the problem                  |
Replace this with your actual dateformat


[Edit: missed a brace]

[Updated on: Wed, 11 July 2007 00:54]

Report message to a moderator

Re: Trying to get info from Date field [message #250715 is a reply to message #250710] Wed, 11 July 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend the opposite (see one of my previous posts): format the input to let Oracle use (standard) index on the table field.

Regards
Michel
Re: Trying to get info from Date field [message #250717 is a reply to message #250715] Wed, 11 July 2007 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would only be necessary if there is an index on the field.
Furthermore, it would be a pain, since we only need a part of the field. The day-component and (if present) the time component have to be ignored.

So, (assuming my crystal ball works) suppose the contents of the db-column is "15-JUL-2007 12:02:21", how would you select fields that contain "JUL-2007" with respect to the index?
Re: Trying to get info from Date field [message #250720 is a reply to message #250717] Wed, 11 July 2007 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on the application and what can be done...

Regards
Michel
Re: Trying to get info from Date field [message #250797 is a reply to message #250640] Wed, 11 July 2007 06:41 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
And from my limited knowledge I believe the table in question is part of Oracle Applications, so that is a beast
Re: Trying to get info from Date field [message #250873 is a reply to message #250797] Wed, 11 July 2007 12:24 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
It's apps/EBS. So, to sort this out:

SQL> desc pa_cost_distribution_lines_all
Name                        Type          ...
EXPENDITURE_ITEM_ID         NUMBER(15)  LINE_NUM                    NUMBER(15)  CREATION_DATE               DATE 
...
PA_PERIOD_NAME              VARCHAR2(15)  
...                                                                                                                 

SQL> 

It's a varchar. Background info: this column is only remotely related to dates, it's part of a pa calendar where you can define your own periods. So, content can also be something like 'corr 2007' (like accounting corrections period for a fiscal year) or 'Q2 2007'.

Edit: removed long lines...

[Updated on: Wed, 11 July 2007 12:25]

Report message to a moderator

Previous Topic: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
Next Topic: why use varchar2(4000)
Goto Forum:
  


Current Time: Mon Dec 05 19:23:04 CST 2016

Total time taken to generate the page: 0.16898 seconds