FIND MONTHS [message #671171] |
Thu, 16 August 2018 10:25 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/0e34224d536ce6589f54f6b98a3d492f?s=64&d=mm&r=g) |
megha25
Messages: 2 Registered: August 2018
|
Junior Member |
|
|
User enters the FROM and TO fields.
FROM: MAY-18
TO: JUL-18
How to retrieve rows in that date range from the below query?
DEFAULT_PERIOD_NAME IS VARCHAR2(15).
select default_period_name from gl.gl_je_batches where
DEFAULT_PERIOD_NAME BETWEEN 'MAY-18' AND 'JUL-18';
I would like to see MAY-18, JUN-18,JUL-18 as the result.
Thanks,
Megha
|
|
|
|
|
|
|
|
Re: FIND MONTHS [message #671188 is a reply to message #671184] |
Thu, 16 August 2018 11:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
If the date range of May-18 through JUL-18 means from the beginning of 05/01/2018 through 07/31/2018 then use
SELECT Default_period_name
FROM Gl.Gl_je_batches
WHERE Default_period_name BETWEEN TO_DATE('MAY-18', 'MON-RR')
AND LAST_DAY(TO_DATE('JUL-18', 'MON-RR'));
If you mean a date range of 05/01/2018 - 06/30/2018 then use
SELECT Default_period_name
FROM Gl.Gl_je_batches
WHERE Default_period_name BETWEEN TO_DATE('MAY-18', 'MON-RR')
AND TO_DATE('JUL-18', 'MON-RR') - 1;
|
|
|
Re: FIND MONTHS [message #671202 is a reply to message #671178] |
Thu, 16 August 2018 12:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
megha25 wrote on Thu, 16 August 2018 11:32
select default_period_name from gl.gl_je_batches where
DEFAULT_PERIOD_NAME BETWEEN 'MAY-18' AND 'JUL-18';
Those are just strings, not dates. M comes after J, so that should return no rows.
|
|
|
|
|
|
Re: FIND MONTHS [message #671224 is a reply to message #671223] |
Fri, 17 August 2018 07:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
I agree 100%. I gave him a solution using his specified data parameters, but he really should be using MAY-2018, not MAY-18 and the to_date would be to_date('MAY-2018','MON-YYYY')
|
|
|