Can Teradata display the Quarter value of a date?

Data Modeling
N/A

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?

Tags (1)
7 REPLIES

Re: Can Teradata display the Quarter value of a date?

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.

Re: Can Teradata display the Quarter value of a date?

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.

N/A

Re: Can Teradata display the Quarter value of a date?

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?

Teradata Employee

Re: Can Teradata display the Quarter value of a date?

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;

N/A

Re: Can Teradata display the Quarter value of a date?

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

Re: Can Teradata display the Quarter value of a date?

Hi ,

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

Cheers,

Raja

Re: Can Teradata display the Quarter value of a date?

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!