start date for the Quarter 2

[ridhirao22]

Hi,

I have a column as YYYYQ. I need to get the Start date and last date of the Quarter:

For ex: 20092
Qtr Start date = 01-APR-2009
Qtr Last date = 01-JUN-2009

How do I do this?
Any help appreciated.

Thanks
RR

 

[Dagon]

There ought to be a neat way of doing that, but the best I've come up with so far is:

WITH INP AS (SELECT '20091' AS INPD FROM DUAL 
             union all 
             SELECT '20092' AS INPD FROM DUAL 
             union all 
             SELECT '20093' AS INPD FROM DUAL
             union all
             SELECT '20094' AS INPD FROM DUAL
             union all
             SELECT '20101' AS INPD FROM DUAL)
select inpd, 
       ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)-1)) q_start,
       ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)))-1 q_end
FROM INP

For Oracle-related work, contact me through Linked-In.

 

[IanWaterman]

Is the field a string or number, assuming its a number do this, if not drop the to_char bit

select
(case when substr(to_char(datefield)5,1) = 1 then to_date('01/04/'||substr(to_char(datefield)1,4), 'DD/MM/YYYY'
else .....repeat for other qtrs
end) as Start_date,
(case when substr(to_char(datefield)5,1) = 1 then to_date('30/04/'||substr(to_char(datefield)1,4), 'DD/MM/YYYY'
else .....repeat for other qtrs
end) as end_date
From Table

Ian

 

[ridhirao22]

Dagon, Thank you for the quick response.

How do I get the First day of the Q_END?

Ian,
Thank you for the quick response as well. But Dagon way seems easy for me to check in the where clause.


Thanks!

 

[Dagon]

WITH INP AS (SELECT '20091' AS INPD FROM DUAL 
             union all 
             SELECT '20092' AS INPD FROM DUAL 
             union all 
             SELECT '20093' AS INPD FROM DUAL
             union all
             SELECT '20094' AS INPD FROM DUAL
             union all
             SELECT '20101' AS INPD FROM DUAL)
select inpd, 
       ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)-1)) q_start,
       TRUNC(ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)))-1, 'MM') q_end_sd,
       ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)))-1 q_end
FROM INP

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

For ex: 20092
Qtr Start date = 01-APR-2009
Qtr Last date = 01-JUN-2009

Unless I'm missing something, shouldn't the last day of Q2 be "30-JUN"?

In any case, the following code seems compact:

select qtr
      ,add_months(to_date(substr(qtr,1,4),'yyyy'),((substr(qtr,-1)-1)*3))     Q_Beg
      ,add_months(to_date(substr(qtr,1,4),'yyyy'),((substr(qtr,-1)-1)*3)+3)-1 Q_End
  from quarters;

  QTR Q_BEG           Q_END
----- --------------- ---------
20092 01-APR-09       30-JUN-09
20093 01-JUL-09       30-SEP-09
20094 01-OCT-09       31-DEC-09
20101 01-JAN-10       31-MAR-10
20102 01-APR-10       30-JUN-10
20103 01-JUL-10       30-SEP-10
20104 01-OCT-10       31-DEC-10
20111 01-JAN-11       31-MAR-11
20112 01-APR-11       30-JUN-11
20113 01-JUL-11       30-SEP-11
20114 01-OCT-11       31-DEC-11

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry, I didn't stop to fully analyze Dagon's code to see that we were probably sharing a brain (across The Pond, no less). <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ridhirao22]

Dagon,Thanks a ton. Appericate you help.

Could you please explain the logic for this part.
3 * TO_NUMBER(SUBSTR(INPD,-1,1)))-1, 'MM')

 

[ridhirao22]

Mufasa, Thank you the reply.

Yes, you are right the last date for the quarter end range in the above ex:is 30-JUN. But my requirement need to get the first day of the last date of the quarter to check against a table data.

 

[SantaMufasa]

Since it's 6:20 p.m. for Dagon (and he might be away from his screen), I'll take a stab at explaining the logic of the complete expression you requested (in order of execution based upon the parentheses):

[b]Code piece[/b]: TRUNC(ADD_MONTHS(TO_DATE(SUBSTR(INPD, 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR(INPD,-1,1)))-1, 'MM') 

1) [b]SUBSTR(INPD, 1,4)[/b]: Beginning at the first character of INPD, take the ensuing four characters (yielding the 4-digit year).

2) [b]TO_DATE(...'YYYY')[/b]: Translate the 4-digit year into a DATE expression. (Since there are neither month nor day values, default the resulting DATE expression to January 1 of the year.)

3) [b]SUBSTR(INPD,-1,1)[/b]: Beginning at the rightmost (i.e., "-1") digit/character of INPD, take the ensuing 1 character (yielding the quarter number). (Note: when using negative start values, the default length is "all characters from the start position to the right end of the value," therefore, the ",1" is optional since we want that one character.)

4) [b]TO_NUMBER(...)[/b]: Transform the one-character quarter into a NUMBER. (Note: character string contains a valid number, and that string is involved in an arithmetic expression, Oracle performs the transformation by default, therefore this function, in this case, is optional.)

5) [b]3 * ...[/b]: Adjust the quarter number to a month number.

6) [b]ADD_MONTHS(<January 1, given year>, <month number>)[/b]: Advance from the beginning of the year, to the month that begins the quarter.

7) [b]TRUNC(...,'MM')[/B]: From the DATE that results from the above ADD_MONTHS function, strip off all DATE/TIME components back to the beginning of the month, i.e. Midnight on the first day of the month.

Now, per your clarification of wanting the first day of the last month of a quarter...that makes my earlier code even simpler. It now could read:

select qtr
      ,add_months(to_date(substr(qtr,1,4),'yyyy'),((substr(qtr,-1)-1)*3))     Q_Beg
      ,add_months(to_date(substr(qtr,1,4),'yyyy'),((substr(qtr,-1)-1)*3)+2) Q_End
  from quarters;

  QTR Q_BEG           Q_END
----- --------------- ---------
20092 01-APR-09       01-JUN-09
20093 01-JUL-09       01-SEP-09
20094 01-OCT-09       01-DEC-09
20101 01-JAN-10       01-MAR-10
20102 01-APR-10       01-JUN-10
20103 01-JUL-10       01-SEP-10
20104 01-OCT-10       01-DEC-10
20111 01-JAN-11       01-MAR-11
20112 01-APR-11       01-JUN-11
20113 01-JUL-11       01-SEP-11
20114 01-OCT-11       01-DEC-11

Let us know if the above is helpful.




Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ridhirao22]

Thank you so much. Appericate your take time. Mufasa

 

[ridhirao22]

Guys,

I used this to get the last month of the quarter.
SELECT TRUNC(ADD_MONTHS(TO_DATE(SUBSTR('19903', 1,4), 'YYYY'), 3 * TO_NUMBER(SUBSTR('19903',-1,1)))-1,'MM') from dual

I thought I should get output as 30-SEP-1990
but I get 31-OCT-1990

I am doing something wrong? I did get the correct ouput when I tried when adviced.

 

[ridhirao22]

I mean it should show as 01-SEP-1990 instead of 01-OCT-1990 in the above sql

 

[karluk]

The month is off in your calculation because you used the TO_DATE function with a 'YYYY' format mask. As it turns out, TO_DATE with this format mask returns the year you expect, but the rest of the date is the first day of the current month. So, rather counterintuitively, "to_date('1990', 'yyyy')" only returns 01-JAN-90 in the month of January, 2011. Now that it is February, "to_date('1990', 'yyyy')" returns '01-FEB-90' and in March it will return '01-MAR-90'.

There are various ways to fix this. Based on your calculation, probably the simplest is to perform the TRUNC right after the TO_DATE and add a 'YYYY' format mask. That will get you to 01-JAN-90 and allow ADD_MONTHS to get to the right month and day.

  1  SELECT ADD_MONTHS(TRUNC(TO_DATE(SUBSTR('19903', 1,4), 'YYYY'), 'YYYY'),
  2*                   3 * TO_NUMBER(SUBSTR('19903',-1,1))-1) from dual
SQL> /

01-SEP-90

 

[SantaMufasa]

Ridhirao,

Oracle has an apparent bug with its DATE processing...If you do a TO_DATE function where you are supplying YEAR only, Oracle supplies the MONTH from SYSDATE instead of defaulting to "January". You can test this by doing the following:

select to_date('1990','yyyy') dt from dual;

DT
----------
01-FEB-90

Then, from your operating-system prompt, change your system's date to a date in some other month:

C:\>date
The current date is: Thu 02/03/2011
Enter the new date: (mm-dd-yy) 05-16-2011

C:\>date
The current date is: Mon 05/16/2011
Enter the new date: (mm-dd-yy)

Then re-query in SQL:

select to_date('1990','yyyy') dt from dual;

DT
---------
01-MAY-90

So, if you want your a workaround for your code, you can concatenate a literal '01' (for January) to your existing code:

SELECT TRUNC(ADD_MONTHS(TO_DATE(SUBSTR('19903', 1,4)||'01', 'YYYYMM'), 3 * TO_NUMBER(SUBSTR('19903',-1,1)))-1,'MM') from dual;

TRUNC(ADD_MONTH
---------------
01-SEP-90

Sorry to be the bearer of "bug news".


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sheesh, Karl, when you post a response in the Oracle forums, could you please immediately send me an e-mail so that I don't cross post with you? <grin> (I obviously type much slower than you do. <another grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ridhirao22]

Thank you, I now understand why I got the right months range when I test the Sql in month of Jan and they differ now in Feb.

Thanks again for all the help. Hope this date check doesn't change next month and problem doesn't comeback again.


RR

 

[ridhirao22]

Mufasa,

Thank you again for a good explaination. Why did you think of when you explain be in the eariler post

2) TO_DATE(...'YYYY'): Translate the 4-digit year into a DATE expression. (Since there are neither month nor day values, default the resulting DATE expression to January 1 of the year.)

 

[ridhirao22]

I tried to change the date and test if my report would work as the months change. Couldn't change the date as I don't have privilage

 

[SantaMufasa]

TO_DATE(...'YYYY'): Translate the 4-digit year into a DATE expression. (Since there are neither month nor day values, default the resulting DATE expression to January 1 of the year.)

As I mentioned, Oracle's 10g behavior is a bug. If I am not mistaken, my explanation matches the documented (proper) behavior. So, my code that incorporates the workaround to this bug is:

select qtr
      ,add_months(to_date(substr(qtr,1,4)||'01','yyyymm'),((substr(qtr,-1)-1)*3))   Q_Beg
      ,add_months(to_date(substr(qtr,1,4)||'01','yyyymm'),((substr(qtr,-1)-1)*3)+2) Q_End
  from quarters;

   QTR Q_BEG           Q_END
------ --------------- ---------
 20092 01-APR-09       01-JUN-09
 20093 01-JUL-09       01-SEP-09
 20094 01-OCT-09       01-DEC-09
 20101 01-JAN-10       01-MAR-10
 20102 01-APR-10       01-JUN-10
 20103 01-JUL-10       01-SEP-10
 20104 01-OCT-10       01-DEC-10
 20111 01-JAN-11       01-MAR-11
 20112 01-APR-11       01-JUN-11
 20113 01-JUL-11       01-SEP-11
 20114 01-OCT-11       01-DEC-11

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”