Dynamic Date Calculation in SQL Query

Database

Dynamic Date Calculation in SQL Query

I'm trying to create a monthly query in BOXI running off of a Teradata database. The query will choose the first day of the 5th month back and the last day of the current month. I tried using the DATEADD function but it did not work. How can I do that in Teradata SQL? Is there an equivalent?
Tags (2)
5 REPLIES
Junior Contributor

Re: Dynamic Date Calculation in SQL Query

You have to play around with ADD_MONTHS and EXTRACT.

d - (EXTRACT(DAY FROM d) - 1) returns the first day of the current month and ADD_MONTHS adds or substracts some months:

SELECT CURRENT_DATE AS d,
ADD_MONTHS(d - (EXTRACT(DAY FROM d) - 1), -5),
ADD_MONTHS(d - (EXTRACT(DAY FROM d) - 1), 1) - 1

Dieter
WAQ
Enthusiast

Re: Dynamic Date Calculation in SQL Query

To get the last day, you can also try
select add_months(current_date,1) - extract(day from add_months(current_date,1));
Junior Supporter

Re: Dynamic Date Calculation in SQL Query

>>"The query will choose the first day of the 5th month back and the last day of the current month."

A little bit cryptic, but elegant, IMHO ;-) :

BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT DATE,
(((DATE/100)-5)*100)+1 (DATE) "Initial",
((((DATE/100)+1)*100)+1 (DATE))-1 "Final"
;

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

Current Date Initial Final
------------ ---------- ----------
2010-11-05 2010-06-01 2010-11-30

HTH.

Cheers.

Carlos.
Junior Contributor

Re: Dynamic Date Calculation in SQL Query

Hi Carlos,
try that query two months later :-)

Dieter
Junior Supporter

Re: Dynamic Date Calculation in SQL Query

Doh!

You're right, Dieter. Ten based arithmetic doesn't fit too well for twelve based years!

Cheers.

Carlos.