Home » SQL & PL/SQL » SQL & PL/SQL » FIND MONTHS (11.2.0.4.0)
FIND MONTHS [message #671171] Thu, 16 August 2018 10:25 Go to next message
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 #671176 is a reply to message #671171] Thu, 16 August 2018 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 26628
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: FIND MONTHS [message #671177 is a reply to message #671171] Thu, 16 August 2018 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select to_char(add_months(to_date('MAY-18','MON-YY'),level-1),'MON-YY') mnths
  2  from dual connect by level <= months_between(to_date('JUL-18','MON-YY'),to_date('MAY-18','MON-YY'))+1
  3  /
MNTHS
------
MAY-18
JUN-18
JUL-18
Re: FIND MONTHS [message #671178 is a reply to message #671176] Thu, 16 August 2018 10:32 Go to previous messageGo to next message
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 #671182 is a reply to message #671178] Thu, 16 August 2018 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 66692
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See my query.

Re: FIND MONTHS [message #671184 is a reply to message #671178] Thu, 16 August 2018 10:39 Go to previous messageGo to next message
BlackSwan
Messages: 26628
Registered: January 2009
Location: SoCal
Senior Member
Please post usable test case that includes CREATE TABLE GL_JE_BATCHES & INSERT statement that populate table with sample data.
What datatype is column DEFAULT_PERIOD_NAME?
Does "JUN-18" exist in table?
BTW - It is bad idea & totally unprofessional to use 2 digit YEAR values. Did you learn nothing from Y2K?
Re: FIND MONTHS [message #671188 is a reply to message #671184] Thu, 16 August 2018 11:08 Go to previous messageGo to next message
Bill B
Messages: 1909
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 messageGo to next message
joy_division
Messages: 4947
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 #671221 is a reply to message #671184] Fri, 17 August 2018 07:47 Go to previous messageGo to next message
EdStevens
Messages: 1142
Registered: September 2013
Senior Member
BlackSwan wrote on Thu, 16 August 2018 10:39
Please post usable test case that includes CREATE TABLE GL_JE_BATCHES & INSERT statement that populate table with sample data.
What datatype is column DEFAULT_PERIOD_NAME?
Does "JUN-18" exist in table?
BTW - It is bad idea & totally unprofessional to use 2 digit YEAR values. Did you learn nothing from Y2K?

The OP very well could have been in diapers at Y2k. Unfortunately (as you and I have both written before) the profession is losing institutional memory of those lessons.
Re: FIND MONTHS [message #671222 is a reply to message #671178] Fri, 17 August 2018 07:48 Go to previous messageGo to next message
EdStevens
Messages: 1142
Registered: September 2013
Senior Member
How is your second post really any different from the first. I see you have added a full query, but it adds no value to the rest of the vague posting.
Re: FIND MONTHS [message #671223 is a reply to message #671171] Fri, 17 August 2018 07:50 Go to previous messageGo to next message
EdStevens
Messages: 1142
Registered: September 2013
Senior Member
And to drive home the point we've made about 2-digit years, when I first read your post, I thought 'May-18' etc, was referring to the 18th day of the month. See how not using 4-digit years just confuses date handling?
Re: FIND MONTHS [message #671224 is a reply to message #671223] Fri, 17 August 2018 07:53 Go to previous message
Bill B
Messages: 1909
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')
Previous Topic: Teradata To Oracle MOD Function
Next Topic: SQL to Subtract X workdays from a date
Goto Forum:
  


Current Time: Thu Nov 21 22:47:34 CST 2019