Home » SQL & PL/SQL » SQL & PL/SQL » Between dates sql
Between dates sql [message #438028] Wed, 06 January 2010 20:16 Go to next message
evergrean
Messages: 15
Registered: February 2008
Junior Member
I fetch reports in our Oracle 9i database each year between 9/1 to 8/31:
Each year I always have to manually change my sql and was wondering if there is a way to fetch the records between 9/1 to 8/31 without changing the year.

For example this will be my query for this year from 9/1/2009 to 8/31/2010:
SELECT *
FROM emp
WHERE HIREDATE between to_date ('2009/09/01', 'yyyy/mm/dd')
AND to_date ('2010/08/31', 'yyyy/mm/dd');


Next year after 8/31/2010 I have to change my query to this to cover from 9/1/2010 to 8/31/2011:
SELECT *
FROM emp
WHERE HIREDATE between to_date ('2010/09/01', 'yyyy/mm/dd')
AND to_date ('2011/08/31', 'yyyy/mm/dd');


Please advise how I create a query that I wont have to change each year and it will automatically fetch the current data from 9/1 to 8/31?
Re: Between dates sql [message #438030 is a reply to message #438028] Wed, 06 January 2010 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
THIS_YEAR := to_char(sysdate,'YYYY')

you need to use CASE along with THIS_YEAR
Re: Between dates sql [message #438046 is a reply to message #438028] Wed, 06 January 2010 23:22 Go to previous messageGo to next message
Anil Kumar B
Messages: 4
Registered: January 2010
Location: Hyderabad
Junior Member
i hope this will solve your problem..

SELECT *
FROM emp
WHERE HIREDATE between
(select case when sysdate<to_date('01-09-' || to_char(sysdate,'YYYY'),'dd-MM-yyyy') then to_date('01-09-' || to_char(to_number(to_char(sysdate,'YYYY'))-1),'dd-MM-yyyy')
else to_date('01-09-' || to_char(sysdate,'YYYY'),'dd-MM-yyyy')
end
from dual)
AND
(select case when sysdate<to_date('31-08-' || to_char(sysdate,'YYYY'),'dd-MM-yyyy') then to_date('31-08-' || to_number(to_char(sysdate,'YYYY')),'dd-MM-yyyy')
else to_date('31-08-' || to_char(to_number(to_char(sysdate,'YYYY'))+1),'dd-MM-yyyy')
end
from dual)
Re: Between dates sql [message #438049 is a reply to message #438046] Wed, 06 January 2010 23:29 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
SELECT *
FROM   emp
WHERE  hiredate BETWEEN (SELECT CASE
                                  WHEN SYSDATE < To_date('01-09-'
                                                         ||To_char(SYSDATE,'YYYY'),'dd-MM-yyyy')
                                  THEN To_date('01-09-'
                                               ||To_char(To_number(To_char(SYSDATE,'YYYY')) - 1),
                                               'dd-MM-yyyy')
                                  ELSE To_date('01-09-'
                                               ||To_char(SYSDATE,'YYYY'),'dd-MM-yyyy')
                                END
                         FROM   dual) AND (SELECT CASE
                                                    WHEN SYSDATE < To_date('31-08-'
                                                                           ||To_char(SYSDATE,'YYYY'),'dd-MM-yyyy')
                                                    THEN To_date('31-08-'
                                                                 ||To_number(To_char(SYSDATE,'YYYY')),'dd-MM-yyyy')
                                                    ELSE To_date('31-08-'
                                                                 ||To_char(To_number(To_char(SYSDATE,'YYYY')) + 1),
                                                                 'dd-MM-yyyy')
                                                  END
                                           FROM   dual) 


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Between dates sql [message #438061 is a reply to message #438049] Thu, 07 January 2010 00:32 Go to previous messageGo to next message
Anil Kumar B
Messages: 4
Registered: January 2010
Location: Hyderabad
Junior Member
Sorry Mr.Black Swan,
please let me know what's wrong in above post.

Re: Between dates sql [message #438088 is a reply to message #438028] Thu, 07 January 2010 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please advise how I create a query that I wont have to change each year

Do not use constant, use variable.
Now it depends of your program/language; for instance, in SQL*Plus:
var year integer
exec year := 2009
select * from emp
where hiredate between to_date(:year||'09/01', 'yyyy/mm/dd')
  and to_date ((:year+1)||'/08/31', 'yyyy/mm/dd');

or better:
var mydate varchar2(20)
exec :mydate := '2009/09/01'
select * from emp
where hiredate between to_date(:mydate, 'yyyy/mm/dd')
  and add_months(to_date(:mydare, 'yyyy/mm/dd'),12)-1;

then you have not to change the query if you want to change the starting day.
Here SQL*Plus does not know DATE variable so I have to use TO_DATE, but if you language knows this DATE datatype, you directly pass the variable to the query without TO_DATE.

Regards
Michel
Re: Between dates sql [message #438089 is a reply to message #438028] Thu, 07 January 2010 01:34 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
ADD_MONTHS (SYSDATE, 12)

Use DBMS_SCHEDULAR or Cron job to run

Regards,
Ved
Re: Between dates sql [message #438099 is a reply to message #438089] Thu, 07 January 2010 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this have to do with the question?

Quote:
Please advise how I create a query that I wont have to change each year and it will automatically fetch the current data from 9/1 to 8/31?


Your answers are getting worse and worse.

Regards
Michel

[Updated on: Thu, 07 January 2010 02:13]

Report message to a moderator

Re: Between dates sql [message #438118 is a reply to message #438099] Thu, 07 January 2010 03:37 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Yep! It was a hint asking the OP to use ADD_MONTHS function not to spoon feed.

Ok, fine next time I will provide the complete answer.

Regards,
Ved

[Updated on: Thu, 07 January 2010 03:48]

Report message to a moderator

Re: Between dates sql [message #438123 is a reply to message #438028] Thu, 07 January 2010 03:49 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
evergrean wrote on Thu, 07 January 2010 03:16
I fetch reports in our Oracle 9i database each year between 9/1 to 8/31:
Each year I always have to manually change my sql and was wondering if there is a way to fetch the records between 9/1 to 8/31 without changing the year.

For example this will be my query for this year from 9/1/2009 to 8/31/2010:
[code]
Please advise how I create a query that I wont have to change each year and it will automatically fetch the current data from 9/1 to 8/31?



This one may help you

Processing ...
SELECT DAT as "My date", -- A date hypothetically your SYSDATE for each day
	ADD_MONTHS(DAT,-8) as "8 months ago", -- 8 months before that date
	TRUNC(ADD_MONTHS(DAT,-8),'YYYY') as "Year of interest", 
		-- Same year as such a date if it's after 01/09, the year before other ways
	ADD_MONTHS(TRUNC(ADD_MONTHS(DAT,-8),'YYYY'),8) as "Between least extreme"
		-- 1st September of the interested year 
FROM (
		SELECT DATE '2009-08-25'+ROWNUM AS DAT 
		FROM DUAL
		CONNECT BY ROWNUM <= 10
	)
Query finished, retrieving results...
     My date         8 months ago    Year of interest Between least exteme 
----------------- ----------------- ----------------- -------------------- 
         26/08/09          26/12/08          01/01/08             01/09/08 
         27/08/09          27/12/08          01/01/08             01/09/08 
         28/08/09          28/12/08          01/01/08             01/09/08 
         29/08/09          29/12/08          01/01/08             01/09/08 
         30/08/09          30/12/08          01/01/08             01/09/08 
         31/08/09          31/12/08          01/01/08             01/09/08 
         01/09/09          01/01/09          01/01/09             01/09/09 
         02/09/09          02/01/09          01/01/09             01/09/09 
         03/09/09          03/01/09          01/01/09             01/09/09 
         04/09/09          04/01/09          01/01/09             01/09/09 

10 row(s) retrieved


Bye Alessandro

[Updated on: Thu, 07 January 2010 03:50]

Report message to a moderator

Re: Between dates sql [message #438129 is a reply to message #438088] Thu, 07 January 2010 04:00 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Instead of Hard coding the value you can maintain a table that contains the two dates and updates the date after each run.


1. Create a procedure that have two dates as input parameter with the sql you wrote using add_months
2. Pick the value from a report table
Create a table like rep_history which contains three columns
Reportid, Startdate,Enddate
3. Pick the date each time when you run and update it .This way you can also track the last run and you dont have to pass the value
and make the process automated.
4. You can use Cron or Java for this.


Regards,
Ved




Re: Between dates sql [message #438188 is a reply to message #438129] Thu, 07 January 2010 07:30 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
May be this is not the good approach but you can also try like this..
You can create a stored procedure and use cron to automate the process.


sql1:

SELECT *
FROM   EMPLOYEES
WHERE  HIRE_DATE BETWEEN TO_DATE('01/01/2009','mm/dd/yyyy') + Numtoyminterval(RN,'YEAR') AND TO_DATE('01/07/2010','mm/dd/yyyy') + Numtoyminterval(RN,'YEAR')


--Substitute the value RN to sql 1
ved@orafaq> SELECT RN
  2  FROM   (SELECT ROWNUM RN,
  3                 2010 + ROWNUM- 1 YR1
  4          FROM   ALL_OBJECTS
  5          WHERE  ROWNUM < 100)
  6  WHERE  YR1 = TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'));

        RN
----------
         1

Elapsed: 00:00:00.00
ved@orafaq> SELECT SYSDATE FROM DUAL;

SYSDATE
---------
07-JAN-10

Elapsed: 00:00:00.00
ved@orafaq> /

SYSDATE
---------
08-JAN-11

Elapsed: 00:00:00.00
ved@orafaq> SELECT RN
  2  FROM   (SELECT ROWNUM RN,
  3                 2010 + ROWNUM- 1 YR1
  4          FROM   ALL_OBJECTS
  5          WHERE  ROWNUM < 100)
  6  WHERE  YR1 = TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'));

        RN
----------
         2

Elapsed: 00:00:00.00
ved@orafaq> 


But I will prefer my previous approach.


[Updated on: Thu, 07 January 2010 07:44]

Report message to a moderator

Previous Topic: substring picturename from path [merged]
Next Topic: problem with heirarical tree level
Goto Forum:
  


Current Time: Sat Oct 01 04:16:30 CDT 2016

Total time taken to generate the page: 0.10776 seconds