FIND MONTHS [message #671171] |
Thu, 16 August 2018 10:25 |
|
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 |
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 |
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 |
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')
|
|
|