1

I am trying to get Which day of the quarter when you give a current date.

For Example if i give 01/25/2012 then the output should be 25. Since it is the 25th day of the quarter.

Similarly if i give 02/01/2012 it should give 32 as output.

I am able to get the first day of the quarter but not able to get which day it is in the quarter.

Platform is Teradata. The Calendar does not have an option for day_of_quarter which is what I am looking for. Given the date, I require to know the number of day of the quarter.

Please help. Thank you in advance.

TD123
  • 27
  • 1
  • 9

2 Answers2

1

This is based on calendar quarter. You can adjust the case statement if you have some sort of business calendar.

select
c.*,
CASE WHEN MONTH(calendar_date) BETWEEN 1 AND 3 
         THEN CAST(((calendar_date / 10000) * 10000) + 101 AS DATE) 
         WHEN MONTH(calendar_date) BETWEEN 4 AND 6 
         THEN CAST(((calendar_date / 10000) * 10000) + 401 AS DATE) 
         WHEN MONTH(calendar_date) BETWEEN 7 AND 9 
         THEN CAST(((calendar_date / 10000) * 10000) + 701 AS DATE) 
         WHEN MONTH(calendar_date) BETWEEN 10 AND 12 
         THEN CAST(((calendar_date / 10000) * 10000) + 1001 AS DATE) 
END as First_Day_Of_Quarter,
calendar_date - first_day_of_quarter + 1 as Day_Of_Quarter
from
sys_calendar.calendar c
where
year_of_calendar = 2014
order by 1 asc
Andrew
  • 8,445
  • 3
  • 28
  • 46
  • Thanks Andrew. But I am looking to replace calendar_date with a date from my table and it doesnt work for that. This is what I am trying to do: – TD123 Dec 02 '14 at 17:30
  • select CASE WHEN MONTH(cast(DayDate as date))) BETWEEN 1 AND 3 THEN CAST(((cast(DayDate as date)) / 10000) * 10000) + 101 AS DATE) WHEN MONTH(cast(DayDate as date)) BETWEEN 4 AND 6 THEN CAST(((cast(DayDate as date)) / 10000) * 10000) + 401 AS DATE) WHEN MONTH(cast(DayDate as date))) BETWEEN 7 AND 9 THEN CAST(((cast(DayDate as date)) / 10000) * 10000) + 701 AS DATE)WHEN MONTH(cast(DayDate as date))) BETWEEN 10 AND 12 THEN CAST(((cast(DayDate as date)) / 10000) * 10000) + 1001 AS DATE)END as First_Day_Of_Quarter, DayDate- first_day_of_quarter + 1 as Day_Of_Quarter from LU_DayComplete – TD123 Dec 02 '14 at 17:31
  • I get a Error 5407 : Invalid operation for datetime and interval – TD123 Dec 02 '14 at 17:43
  • That SQL looks invalid to me. You've got too many closing parens in your **when** statements: `WHEN MONTH(cast(DayDate as date))) BETWEEN 1 AND 3`. And what data type is `DayDate`? – Andrew Dec 02 '14 at 17:54
1

TRUNC(datecol) returns the first day of the quarter, date1 - date2 returns the number of days between:

SELECT (datecol - TRUNC(datecol, 'Q')) + 1 AS day_of_quarter

You might put this calculation into a SQL UDF or add it as a column to your existing calendar table.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • How does the rounding of TRUNC() on the 16th day of the 2nd quarter impact this @dnoeth? – Rob Paller Dec 02 '14 at 18:23
  • @RobPaller, I was wondering about that too. I tried it with my query below, and got the exact same results as the case statement. – Andrew Dec 02 '14 at 18:57
  • @RobPaller, TRUNC truncates while ROUND rounds :-) – dnoeth Dec 02 '14 at 19:10
  • @dnoeth, from the [documentation](http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/ch07.077.134.html#ww18264021): `Quarter (rounds up on the 16th day of the 2nd month of the quarter)`. No clue what that's actually saying. – Andrew Dec 02 '14 at 19:14
  • It's a mistake in the manual. The parameter explanation for TRUNC() is shared by that for ROUND(). I read it twice today and it didn't dawn on me. – Rob Paller Dec 02 '14 at 19:17