Database
N/A

## 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
Senior Apprentice

## 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
Enthusiast

## Re: Dynamic Date Calculation in SQL Query

To get the last day, you can also try
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.
Senior Apprentice

## 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.