Trying to get info from Date field [message #250640] |
Tue, 10 July 2007 13:31  |
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 #250646 is a reply to message #250640] |
Tue, 10 July 2007 13:44   |
joy_division
Messages: 4963 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 #250664 is a reply to message #250640] |
Tue, 10 July 2007 14:36   |
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 #250710 is a reply to message #250672] |
Wed, 11 July 2007 00:54   |
Frank
Messages: 7901 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 #250717 is a reply to message #250715] |
Wed, 11 July 2007 01:12   |
Frank
Messages: 7901 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 #250873 is a reply to message #250797] |
Wed, 11 July 2007 12:24  |
skooman
Messages: 913 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
|
|
|