Home » SQL & PL/SQL » SQL & PL/SQL » How to get dates with six months difference
How to get dates with six months difference [message #212874] Mon, 08 January 2007 12:29 Go to next message
swamy99
Messages: 30
Registered: June 2006
Member
I would like to know how we can get start date and end dates with 6 months apart where I have single date column having several dates from 01-Jan-2000 onwards and all the way to 31-Dec-2006 in a table.
Ex:

start_date End_date
01-Jan-2001 30-Jun-2001
01-Jul-2001 31-Dec-2001
01-Jan-2002 30-Jun-2002
01-Jul-2002 31-Dec-2002
01-Jan-2003 30-Jun-2003
01-Jul-2003 31-Dec-2003
.....
.....
01-Jan-2006 30-Jun-2006
01-Jul-2006 31-Dec-2006


Thanks,
Re: How to get dates with six months difference [message #212875 is a reply to message #212874] Mon, 08 January 2007 12:31 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Is the single date column the start or end date for the 6 month range?
Re: How to get dates with six months difference [message #212876 is a reply to message #212875] Mon, 08 January 2007 12:41 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
Correct. So that I can use this in the loop in the main query where clause. Like date_field between start_date and End_date in the main query which is in the loop.
Re: How to get dates with six months difference [message #212877 is a reply to message #212874] Mon, 08 January 2007 12:49 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
a) is it the start date

b) is it the end date

Answer: Correct

Please clarify
Re: How to get dates with six months difference [message #212878 is a reply to message #212877] Mon, 08 January 2007 13:03 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
OK. Here is an example with the table dual.

select distinct to_char(trunc(add_months(sysdate,1),'Q'),'DD-Mon-YYYY') start_month,
to_char(trunc(add_months(sysdate,6),'Q')-1,'DD-Mon-YYYY') end_month
from dual
where sysdate > '31-DEC-2006';


START_MONTH|END_MONTH
01-Jan-2007|30-Jun-2007


But in my case I have a table with a date_field in a table with dates ranging from Jan 2000 to Dec 2006. I would like to use the same date_field and identify that it lies between 6 months from January to June which will be the start_date and from July to December as End_date.
Re: How to get dates with six months difference [message #212880 is a reply to message #212874] Mon, 08 January 2007 13:26 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I think what you are asking for is given a date,
query a date_range table and find the start and ending date for that date.

If that is the problem then this is one solution.

select max(start_date) st_date, min(end_date) end_date from date_range_table
where start_date <= (select tst_date from date_table) and end_date > (select tst_date from date_table)


or

select start_date, end_date from
date_range_table where TST_DATE between
start_date and end_date


Note: Both untested
Hope this helps.

[Updated on: Mon, 08 January 2007 13:33]

Report message to a moderator

Re: How to get dates with six months difference [message #212883 is a reply to message #212880] Mon, 08 January 2007 14:06 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
I have a table with a date_field that has dates from 01-Jan-2000 through 31-Dec-2006. Using this date_field, I would like to get a start_date and End_date based on six months difference. As I have provided in the example

select distinct to_char(trunc(add_months(sysdate,1),'Q'),'DD-Mon-YYYY') start_month,
to_char(trunc(add_months(sysdate,6),'Q')-1,'DD-Mon-YYYY') end_month
from dual
where sysdate > '31-DEC-2006';


just substitute with the sysdate with date_field and dual table with the actual table and the where condition to '31-DEC-1999'. I am expecting to get the results as shown below.

start_date End_date
01-Jan-2001 30-Jun-2001
01-Jul-2001 31-Dec-2001
01-Jan-2002 30-Jun-2002
01-Jul-2002 31-Dec-2002
01-Jan-2003 30-Jun-2003
01-Jul-2003 31-Dec-2003
.....
.....
01-Jan-2006 30-Jun-2006
01-Jul-2006 31-Dec-2006
Re: How to get dates with six months difference [message #212884 is a reply to message #212874] Mon, 08 January 2007 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
It looks OK to me. Congratulations!
icon13.gif  Re: How to get dates with six months difference [message #212887 is a reply to message #212884] Mon, 08 January 2007 14:20 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
But not getting the desired output as expected. I think you did not get what I am looking for.

Table_data
01-Jan-2000
02-Jan-2000
03-Jan-2000
.....
.....
28-Dec-2006
29-Dec-2006
30-Dec-2006
31-Dec-2006

Now I want to have two columns Start_Date and End_date from the above table. All the dates starting from 01-Jan-2000 through 30-Jun-2000 should come under Start_date as 01-Jan-2000 and End_date as 30-Jun-2000. The next row start_date will be from 01-Jul-2000 and end_date will be 31-DEC-2000 and so on until 31-Dec-2006.

The result I am looking for should be as shown in the first posting. Hope this is clear.

[Updated on: Mon, 08 January 2007 14:21]

Report message to a moderator

Re: How to get dates with six months difference [message #213007 is a reply to message #212887] Tue, 09 January 2007 02:18 Go to previous messageGo to next message
swamy99
Messages: 30
Registered: June 2006
Member
I am not sure why the posting that I started as Date Query in SQL Expert section was locked inspite of mentioning that I was not able to get the solution here in this section.
Anyway, the solutions provided by MarcL was not I was looking for Sorry MarcL. Nor this is a beginners homework problem as mentioned by another expert. This is a problem for generating a pivot result but not expecting exactly a pivot result as there are several missing dates in between.
Even if that is the beginner case, what was the problem for providing a solution as this section is for all newbies, RIGHT?

I think whoever responded to this problem did not understand the exact situation. I have requested just a part of the problem which I need to use in a complex query. If they understood the problem, they could have given a proper solution. I gave an example query on how to get the dates ie whatever lies between '01-JAN-2000' and '30-JUN-2000' ie if the date is '24-FEB-2000' then it should have start_date as '01-JAN-2000' and end_date as '30-JUN-2000' and whatever lies between '01-JUL-2000' and '31-DEC-2000' ie if the date is '20-SEP-2000' then it should have start_date as '01-JUL-2000' and end_date as '31-DEC-2000'.

Well anyway, I figured it out on how to get the desired results. Thanks to all those who tried to help.
Re: How to get dates with six months difference [message #213008 is a reply to message #213007] Tue, 09 January 2007 02:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I explained why I locked the other topic: You should not have started a new topic in another forum.

Quote:
I think whoever responded to this problem did not understand the exact situation. I have requested just a part of the problem which I need to use in a complex query. If they understood the problem, they could have given a proper solution


You cannot blame the people here for not understanding your problem. If nobody here really understood your problem, apparently you did not explain it too well.
Re: How to get dates with six months difference [message #213040 is a reply to message #212874] Tue, 09 January 2007 03:40 Go to previous messageGo to next message
Giraffe
Messages: 10
Registered: January 2007
Location: Suffolk, UK
Junior Member
Is this something like you're after?

SELECT ADD_MONTHS(TRUNC(&dat, 'YYYY'), CASE WHEN EXTRACT (MONTH FROM &dat) > 6 THEN 6 ELSE 0 END) startdate
     , ADD_MONTHS(TRUNC(&dat, 'YYYY'), CASE WHEN EXTRACT (MONTH FROM &dat) > 6 THEN 12 ELSE 6 END) - 1 enddate
FROM   dual;


Replacing the &dat variable with the value selected from your table should give you the desited results. Note that the 'enddate' column is midnight on (e.g.) 30th June, and the next startdate would be midnight on the 1st July, so if you are doing any comparisons with dates rather than just displaying them, you should be aware of the 'missing' 24 hours.
Re: How to get dates with six months difference [message #213190 is a reply to message #213040] Tue, 09 January 2007 14:22 Go to previous message
swamy99
Messages: 30
Registered: June 2006
Member
Thanks Giraffe,
Unfortunately, I do not want to pass any variables. But thanks for your suggestions. I did get the desired results using the following query.

select distinct to_char(trunc(add_months(c.date_field,1),'Q'),'DD-Mon-YYYY') start_month,
to_char(trunc(add_months(c.date_field,6),'Q')-1,'DD-Mon-YYYY') end_month
from test_table c
where c.date_field > '31-Dec-1983' and
to_char(trunc(add_months(c.date_field,1),'Q'),'Mon') in ('Jan','Jul')
and to_char(trunc(add_months(c.date_field,6),'Q')-1,'Mon') in ('Jun','Dec')
order by substr(start_month,-4)

START_MONTH|END_MONTH
01-Jul-1984|31-Dec-1984
01-Jan-1985|30-Jun-1985
01-Jul-1985|31-Dec-1985
01-Jan-1986|30-Jun-1986
01-Jul-1986|31-Dec-1986
01-Jan-1987|30-Jun-1987
01-Jul-1987|31-Dec-1987
01-Jan-1988|30-Jun-1988
01-Jul-1988|31-Dec-1988
01-Jan-1989|30-Jun-1989
01-Jul-1989|31-Dec-1989
01-Jan-1990|30-Jun-1990
01-Jul-1990|31-Dec-1990
01-Jan-1991|30-Jun-1991
01-Jul-1991|31-Dec-1991
01-Jan-1992|30-Jun-1992
01-Jul-1992|31-Dec-1992
01-Jan-1993|30-Jun-1993
01-Jul-1993|31-Dec-1993
01-Jan-1994|30-Jun-1994
01-Jul-1994|31-Dec-1994
01-Jan-1995|30-Jun-1995
01-Jul-1995|31-Dec-1995
01-Jan-1996|30-Jun-1996
01-Jul-1996|31-Dec-1996
01-Jan-1997|30-Jun-1997
01-Jul-1997|31-Dec-1997
01-Jan-1998|30-Jun-1998
01-Jul-1998|31-Dec-1998
01-Jan-1999|30-Jun-1999
01-Jul-1999|31-Dec-1999
01-Jan-2000|30-Jun-2000
01-Jul-2000|31-Dec-2000
01-Jan-2001|30-Jun-2001
01-Jul-2001|31-Dec-2001
01-Jan-2002|30-Jun-2002
01-Jul-2002|31-Dec-2002
01-Jan-2003|30-Jun-2003
01-Jul-2003|31-Dec-2003
01-Jan-2004|30-Jun-2004
01-Jul-2004|31-Dec-2004
01-Jan-2005|30-Jun-2005
01-Jul-2005|31-Dec-2005
01-Jan-2006|30-Jun-2006
01-Jul-2006|31-Dec-2006
01-Jan-2007|30-Jun-2007


Sorry Frank, Probably I might have not expressed correctly. I will try to give full details of the problem next time. Thanks and let me know if there is any problem with the above query results.
Previous Topic: foreign key
Next Topic: How 2 c d structure of view....
Goto Forum:
  


Current Time: Fri Dec 09 17:20:19 CST 2016

Total time taken to generate the page: 0.29327 seconds