All Forums Data Modeling
MarkL 1 post Joined 01/13
09 May 2013
Can Teradata display the Quarter value of a date?

I have a date column called reporting period date and I only want to pull data from this table where reporting period date = Q1, Q2, etc...
How would I format my reporting period date so that it represents a Quarter?

VandeBergB 182 posts Joined 09/06
09 May 2013

Join your reporting date to the sys_calendar.calendar view and you can pull "quarter_of_year" from the system calendar.  If you need to shift the QOY field to represent a fiscal or other operating interpretation, it's fairly simple and a painless to build a semantic calendar view on top of the system calendar.

Some drink from the fountain of knowledge, others just gargle.

CarlosAL 512 posts Joined 04/08
10 May 2013

Mark:
You can calculate the quarter from dates as ((CAST(EXTRACT(MONTH FROM fecha) AS BYTEINT)-1)/4)+1
 
 BTEQ -- Enter your SQL request or BTEQ command:
SELECT DATE, ((CAST(EXTRACT(MONTH FROM DATE) AS BYTEINT)-1)/4)+1 QUARTER;

 *** Query completed. One row found. 2 columns returned.
 *** Total elapsed time was 1 second.

Current Date      QUARTER
------------  -----------
  2013-05-10            2
HTH.
Cheers.
Carlos.

dw31 4 posts Joined 01/10
12 Nov 2013

Well, I just ran this in Teradata 14. Today is 2013-11-12, and that's the fourth quarter, right? My result was 3. What gives?

Jake Kurdsjuk 9 posts Joined 06/13
12 Nov 2013

Which did you run, against SYS_CALENDAR or the math above?  I believe the math above should be division by 3 and not 4.
 
SELECT DATE, ((CAST(EXTRACT(MONTH FROM DATE) AS BYTEINT)-1)/3)+1 QUARTER;

Jake Kurdsjuk
Product Manager
Teradata Communications Data Model

dw31 4 posts Joined 01/10
12 Nov 2013

Yes, it was the SQL, and that's the fix Jake. Thanks.

Raja_KT 1246 posts Joined 07/09
12 Nov 2013

Hi ,
You can use quarter_of_year or  quarter_of_calendar columns in sys_calendar and then join with your table.
Cheers,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

shavyani 23 posts Joined 03/15
07 Apr 2015

Hi folks,
Im finding an issue handling these quarter dates in terdata.
My requirement is to fetch the Q1 and Q2 for current year and previous year in a single query and this query I will be using as subquery  because “ current year” and “previous years” keeps changing.
The actual WHERE CLAUSE  is:
WHERE
FSCL_QTR_NUM IN (210401,201402,201501,201502)­­­ -->  current years and previous years qtr num
AND FSCL_QTR_NUM >201401
AND FSCL_QTR_NUM <201502
The above should be replaced by :
WHERE
FSCL_QTR_NUM IN(single_subquery)
AND FSCL_QTR_NUM > single_subquery
AND FSCL_QTR_NUM < single_subquery
NOTE: so based on this subquery the FSCL_QTR_NUM should be fetched automatically for each of the forthcoming years,i.e (it fetches Q1and Q2 for current year and previous year that keeps changing)
Please help me with this!
 
 
 

You must sign in to leave a comment.