Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: February 03, 2003 - 3:01 pm UTC

Last updated: June 22, 2006 - 10:43 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Is there a way to return the start date , end date and the quarter in a single select statement.

eg. If sysdate is 02-03-2003

START_DATE END_DATE YYYY_Q
---------- ---------- ---------
01-01-2003 03-31-2003 2003 - 1

I have a process that must be run every quarter but on the Last day.
This process does some processing by selecting data between the date ranges. I can not format the where in the select as it call a properitory code with needs the date range.

I am using dbms_job which calls a function that return a boolean
(if <funct> then <do_process> .....), and a out parameter of start and end date, the input is defaulted to sysdate. The date ranges are assumed in the function based on the Q value returned.

I though maybe there is a easier way to do it with a single select.

Thanks in advance.


and Tom said...

If you run this on the last day -- sure, just replace DT with SYSDATE and select the three columns I do from DUAL (instead of all_objects which I'm just using to generate data)

ops$tkyte@ORA920>
ops$tkyte@ORA920> select trunc(add_months(dt,-2),'mm') start_date,
2 dt end_date,
3 to_char( dt, 'yyyy - q' ) qtr
4 from (
5 select add_months( '31-dec-2002', rownum*3) dt
6 from all_objects
7 where rownum <= 10
8 )
9 /

START_DAT END_DATE QTR
--------- --------- --------
01-JAN-03 31-MAR-03 2003 - 1
01-APR-03 30-JUN-03 2003 - 2
01-JUL-03 30-SEP-03 2003 - 3
01-OCT-03 31-DEC-03 2003 - 4
01-JAN-04 31-MAR-04 2004 - 1
01-APR-04 30-JUN-04 2004 - 2
01-JUL-04 30-SEP-04 2004 - 3
01-OCT-04 31-DEC-04 2004 - 4
01-JAN-05 31-MAR-05 2005 - 1
01-APR-05 30-JUN-05 2005 - 2

10 rows selected.


Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

good

A reader, February 04, 2003 - 1:58 am UTC

dint know sql cud be used like this

Changed query to run by sysdate

Saru, February 04, 2003 - 9:22 am UTC

Thanks Tom,

I changes the query, to use sysdate, and to select only on current year.

select trunc(add_months(dt,-2),'mm') start_date
,dt end_date
,to_char( dt, 'yyyy - q' ) qtr
from (select add_months( trunc(sysdate,'Y') -1 , rownum*3) dt
from all_objects
where rownum <= 4)
where sysdate between trunc(add_months(dt,-2),'mm') and dt;

I still think there is a better way to do it by using dual.

Excellent trick to take the last day of the year as the base date.

Thanks

Saru, February 04, 2003 - 9:36 am UTC

I got it... :)

select trunc(add_months(add_months(trunc(sysdate,'Y') -1 ,to_number(to_char(sysdate,'Q')) * 3),-2),'MM') start_date
,add_months(trunc(sysdate,'Y') -1 ,to_number(to_char(sysdate,'Q')) * 3) end_date
,to_char(sysdate,'YYYY - q') qtr from dual
/


Slightly simpler version

Andrew, February 04, 2003 - 10:15 am UTC

Before reading this I didn't know trunc() did different levels of truncation - incredibly useful information just when I needed it!

This version avoids all the to_char/to_number conversions (apart from the qtr string):

SELECT trunc(SYSDATE) AS "SYSDATE",
trunc(SYSDATE, 'q') AS "Q_START",
add_months(trunc(SYSDATE, 'q'), 3) - 1 AS "Q_END",
to_char(SYSDATE, 'yyyy - q' ) AS "QTR"
FROM dual


Date Scaling

Fred, September 02, 2003 - 3:15 pm UTC

I have a similar situation. Let's say I have a simple table like this:

SEQUENCE_NUMBER EFFECTIVE_DATE
6 20021223
6 20021020
6 20021017
6 20021011
...
...

And I want the output of a query to be like:

Start_Date End_Date
12/23/02 00/00/00
10/20/02 12/22/02
10/17/02 10/19/02
10/11/02 10/16/02
...
...

Where the End_Date column of the very first row will always be 00/00/00 and the subsequent ones are 1 day subtracted from the Start_Date column of the previous row.

Now I am able to do the scaling (subtract 1 day) part, but I haven't figured out how to make the first row always 00/00/00.

Could you offer some insight?

Tom Kyte
September 02, 2003 - 9:11 pm UTC 

ops$tkyte@ORA920> select to_char( to_date( edate,'yyyymmdd'), 'mm/dd/yy' ),
  2         nvl( to_char( to_date( lag(edate-1) over (order by edate desc),'yyyymmdd'), 'mm/dd/yy'), '00/00/00' )
  3    from t
  4   order by edate desc ;
 
TO_CHAR( NVL(TO_C
-------- --------
12/23/02 00/00/00
10/20/02 12/22/02
10/17/02 10/19/02
10/11/02 10/16/02

Never mind

fred, September 02, 2003 - 7:23 pm UTC 

I got help.

SQL> select * from t_edate;
 
SEQUENCE_NUMBER EFFECTIVE
--------------- ---------
              6 23-DEC-02
              6 20-OCT-02
              6 17-OCT-02
              6 11-OCT-02
              8 10-DEC-02
              8 30-DEC-02
 
6 rows selected.
 
  1  select
  2    b.sequence_number, to_char(b.effective_date,'MM/DD/YY'), nvl(to_char(a.effective_date-1,'MM/DD/YY'),'00/00/00')
  3  from
  4    (select sequence_number,effective_date, rownum rnum from
  5     (select * from t_edate order by sequence_number, effective_date desc)) a,
  6    (select sequence_number,effective_date,rownum-1 rnum from
  7     (select * from t_edate order by sequence_number, effective_date desc)) b
  8  where
  9    b.rnum=a.rnum (+) and
 10    b.sequence_number=a.sequence_number (+)
 11* order by 1 desc, 2 desc
SQL> /
 
SEQUENCE_NUMBER TO_CHAR( NVL(TO_C
--------------- -------- --------
              8 12/30/02 00/00/00
              8 12/10/02 12/29/02
              6 12/23/02 00/00/00
              6 10/20/02 12/22/02
              6 10/17/02 10/19/02
              6 10/11/02 10/16/02
 
6 rows selected.

Tom Kyte
September 02, 2003 - 9:19 pm UTC

see above -- much easier with analytics!!!

So Simple, Yet Effective

fred, September 03, 2003 - 9:58 am UTC

Now I will sit in the corner with the good ol' dunce cap on my head. I must overcome... ughhh.

Thanks.

Fred

Generate one month date with or without date

Sean, January 09, 2004 - 4:44 pm UTC

Hi Tom,

create table t1(c1 date, c2 varchar2(20));

insert into t1
values(sysdate-5, 'A');

insert into t1
values(sysdate, 'B');

I would like to use a query to generate whole month with or without data.
someting like:

01-JAN-04
02-JAN-04
03-JAN-04
04-JAN-04 A
05-JAN-04
06-JAN-04
07-JAN-04
08-JAN-04
09-JAN-04 B
.
.
.
31-JAN-04


Thanks so much for your help.

Sean

Tom Kyte
January 10, 2004 - 10:23 am UTC 

ops$tkyte@ORA9IR2> /*
DOC>drop table t;
DOC>
DOC>create table t ( c1 date, c2 varchar2(1) );
DOC>
DOC>insert into t values ( sysdate-5, 'a' );
DOC>insert into t values ( sysdate, 'b' );
DOC>*/
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x.dt, t.c2
  2    from (
  3  select first+rownum-1 dt
  4    from ( select trunc(last_day(max(c1))) last, trunc(min(c1),'mm') first from t ),
  5         all_objects
  6   where rownum <= last-first+1
  7         ) x,
  8             t
  9   where x.dt = trunc(t.c1(+))
 10  /
 
DT        C
--------- -
01-JAN-04
02-JAN-04
03-JAN-04
04-JAN-04
05-JAN-04 a
06-JAN-04
07-JAN-04
08-JAN-04
09-JAN-04
10-JAN-04 b
11-JAN-04
12-JAN-04
13-JAN-04
14-JAN-04
15-JAN-04
16-JAN-04
17-JAN-04
18-JAN-04
19-JAN-04
20-JAN-04
21-JAN-04
22-JAN-04
23-JAN-04
24-JAN-04
25-JAN-04
26-JAN-04
27-JAN-04
28-JAN-04
29-JAN-04
30-JAN-04
31-JAN-04
 
31 rows selected.

Of course, you need not use the ( select trunc(last_day(max(c1))) last, trunc(min(c1),'mm') first from t ) if you KNOW the start/stop dates.  You would just make the query be:
ops$tkyte@ORA9IR2> variable f varchar2(25);
ops$tkyte@ORA9IR2> variable s varchar2(25);
ops$tkyte@ORA9IR2> exec :f := '01-jan-2004'; :s := '31-jan-2004';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select x.dt, t.c2
  2    from (
  3  select to_date(:f,'dd-mon-yyyy')+rownum-1 dt
  4    from all_objects<b>
  5   where rownum <= to_date(:s,'dd-mon-yyyy')-to_date(:f,'dd-mon-yyyy')+1</b>
  6         ) x,
  7             t
  8   where x.dt = trunc(t.c1(+))
  9  /

and you might even:
ops$tkyte@ORA9IR2> select x.dt, t.c2
  2    from (
  3  select to_date(:f,'dd-mon-yyyy')+rownum-1 dt
  4    from all_objects
  5   where rownum <= to_date(:s,'dd-mon-yyyy')-to_date(:f,'dd-mon-yyyy')+1
  6         ) x,<b>
  7         (select *
  8            from t
  9           where c1 between to_date(:f,'dd-mon-yyyy') and to_date(:s,'dd-mon-yyyy')
 10         ) t</b>
 11   where x.dt = trunc(t.c1(+))
 12  /

Similar problem...

Neeti, February 20, 2004 - 11:41 am UTC


Hi Tom,

I have a similar problem. I have a report which accepts
a parameter in the form of YYYYQ and the requirement is
this parameter to be transformed into last day of that
quarter to use in a select statement.e.g. if I pass 19981
then this should be transformed into 03311998(last day of
first quarter)

This I use currently...

select last_day(to_date (substr( '19981', 1,4) ||
decode(substr('19981', 5,1),'1', '03', '2','06', '3'
,'09', '4', '12'), 'YYYYMM'))
from dual;

Is there a way I can improve this SQL..?

Thanks,
Vimal

Tom Kyte
February 20, 2004 - 1:17 pm UTC

select last_day(to_date( substr(:x,1,4) || (mod(:x,10)*3), 'yyyymm')) from dual;

is "shorter"

Need Some Help

A reader, June 21, 2006 - 9:15 am UTC


Hello Tom,

I need a query in this format since our fiscal year run from 10/1/xxxx to 9/30/xxxx and
then I need to get the total payment during that fiscal year.

How can I have something in this format..

Fiscal Start Fiscal End Total Payments
10/1/1986 9/30/1987 0.00
10/1/1987 9/30/1988 0.00
10/1/1988 9/30/1989 11,448.00
10/1/1989 9/30/1990 15,853.71
10/1/1990 9/30/1991 19,096.00
10/1/1991 9/30/1992 23,762.72
10/1/1992 9/30/1993 37,481.13
10/1/1993 9/30/1994 38,374.44
10/1/1994 9/30/1995 39,540.46
10/1/1995 9/30/1996 40,659.17

Thank you.l


Tom Kyte
June 22, 2006 - 10:43 am UTC

ok, good, I guess you should write one?


no create
no inserts
NO EXPLANATION of the inputs or outputs...

no look.




more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_JOB

More on PL/SQL routine DBMS_JOB here