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?
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.
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
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?
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;
You can use quarter_of_year or quarter_of_calendar columns in sys_calendar and then join with your table.
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:
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 :
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!