Equivalent function for DATEADD

Database

Equivalent function for DATEADD

Hi all,

       I need to calculate 8 weeks of data is there any function related to this??

Tags (1)
5 REPLIES
Junior Contributor

Re: Equivalent function for DATEADD

What do you need exactly?

There simple math on DATEs like DATE +/- n, there's ADD_MONTHS(DATE or TIMESTAMP, n), there are INTERVALs...

Dieter

Re: Equivalent function for DATEADD

Hi,

I need to get data of  8 consucutive weeks from the current date by displaying first Monday  of each week. I have used this query which will give 1 week data by displaying first  monday  of the week.Smilar to this need to get 8 consecutive weeks from the date when report is refreshed

CASE WHEN DAYOFWEEK(APPOINTMENT_START_DATE.BI_ACTIVITY_DATE-2) = 7 THEN  APPOINTMENT_START_DATE.BI_ACTIVITY_DATE

ELSE APPOINTMENT_START_DATE.BI_ACTIVITY_DATE-(DAYOFWEEK(APPOINTMENT_START_DATE.BI_ACTIVITY_DATE-2)) END+0

Junior Contributor

Re: Equivalent function for DATEADD

DAYOFWEEK is no Teradata syntax, might be a UDF installed at your system. Anyway it's returning the weekday based on sunday as starting day. But you need ISO weeks :-)

What is your TD release? 

In TD14.10 there's a built-in function:

TD_WEEK_BEGIN(date, 'ISO')

Before you might use this calculation:

REPLACE FUNCTION WEEK_BEGIN(cdate DATE)
RETURNS DATE
SPECIFIC week_begin_DA
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN
cdate-(((cdate - DATE '0001-01-01')) MOD 7);

If you're not on TD13.10 or you can't create SQL-UDFs you can simply use the formula as-is:

APPOINTMENT_START_DATE.BI_ACTIVITY_DATE-(((APPOINTMENT_START_DATE.BI_ACTIVITY_DATE - DATE '0001-01-01')) MOD 7)

Adding 8 weeks is just x + 8*7.

Do you need a BETWEEN week_begin AND week_begin + 8*7 -1 or just the 8 mondays?

Then you might use EXPAND on in TD13.10:

SELECT BEGIN(pd)
FROM sys_calendar.CALENDAR
WHERE calendar_date = DATE
EXPAND ON PERIOD(WEEK_BEGIN(calendar_date), WEEK_BEGIN(calendar_date) + 8*7) AS pd
BY ANCHOR MONDAY

Dieter

Re: Equivalent function for DATEADD

Hello,

Our database automatically defaults to GMT time and when we used SQL Server, we had to minus -7 hours for arizona time.

The function in SQL we use to do was dateadd(HH,-7, orig_ord_dt_tm) and I am having trouble finding something similar in Teradata. 

Any help would be great. 

Maria

Junior Contributor

Re: Equivalent function for DATEADD

What's the datatype of orig_ord_dt_tm, does it include WITH TIME ZONE?

This returns exactly what you did in SQL Server:

orig_ord_dt_tm - INTERVAL '7' HOUR

You might also get a TIMESTAMP WITH TIME ZONE:

orig_ord_dt_tm AT -7                  -- fixed at -07:00
orig_ord_dt_tm AT 'America Mountain' -- using Daylight Saving Time