0

I have current_date in Teradata which 18 DEC 2019

I have to calculate the previous quarter start date and end date from the above current_date.

Input               =     '2019-12-18'
Output Start Date   =     '2019-07-01' 
Output End Date     =     '2019-09-30'
Sandesh Gitte
  • 11
  • 1
  • 3
  • What have you tried so far? SO is not a place to ask for complete code to be done for you. You need to make an attempt and we can help if you have any specific problems during that attempt – Prebsus Dec 18 '19 at 06:51

1 Answers1

2

You should be able to do this using the TRUNC function, something like:

SELECT 
  TRUNC(ADD_MONTHS(CURRENT_DATE, -3), 'Q') AS Start_Quarter, -- Previous quarter start
  TRUNC(CURRENT_DATE, 'Q') - 1 AS End_Quarter -- Current quarter start date - 1 day

Give it a try and let me know. This assumes the mistake in the manual is still considered a "mistake".

Also, depending on what TD version you're using, you may be able to use built-in functions: 

SELECT 
  TD_QUARTER_BEGIN(CURRENT_DATE) AS Start_Quarter,
  TD_QUARTER_END(CURRENT_DATE) AS End_Quarter

Reference
TD Manual
Built-in functions

ravioli
  • 3,749
  • 3
  • 14
  • 28
  • 1
    To get the previous quarter: `Trunc(Add_Months(Current_Date, -3), 'Q') AS Start_Quarter, Trunc(Current_Date, 'Q') - 1 AS End_Quarter`. But more important: don't use `TD_QUARTER_BEGIN` because it's based on the stupid business calendar and might not return the expected result unless you force `TD_QUARTER_BEGIN(CURRENT_DATE, 'TERADATA')`. And it will fail outside of the years 1900 to 2100 while TRUNC works for all dates. – dnoeth Dec 18 '19 at 09:24
  • Oops, I forgot it was for the previous quarter. Updated. Thanks Dieter. – ravioli Dec 18 '19 at 11:14
  • Thank you very much @ravioli ,@dnoeth for your help! – Sandesh Gitte Dec 19 '19 at 08:27
  • Happy to help :) – ravioli Dec 19 '19 at 08:28