All Forums Database
Monica_a 2 posts Joined 09/13
18 Sep 2013
Equivalent function for DATEADD

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

Tags:
dnoeth 4628 posts Joined 11/04
18 Sep 2013

 
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

Dieter

Monica_a 2 posts Joined 09/13
18 Sep 2013

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

 

dnoeth 4628 posts Joined 11/04
18 Sep 2013

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
 
 

Dieter

mcalleja74 1 post Joined 07/16
21 Jul 2016

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

dnoeth 4628 posts Joined 11/04
21 Jul 2016

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

 

Dieter

You must sign in to leave a comment.