What do you need exactly?
There simple math on DATEs like DATE +/- n, there's ADD_MONTHS(DATE or TIMESTAMP, n), there are INTERVALs...
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
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:
Before you might use this calculation:
REPLACE FUNCTION WEEK_BEGIN(cdate DATE)
RETURNS NULL ON NULL INPUT
INLINE TYPE 1
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:
WHERE calendar_date = DATE
EXPAND ON PERIOD(WEEK_BEGIN(calendar_date), WEEK_BEGIN(calendar_date) + 8*7) AS pd
BY ANCHOR MONDAY
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.
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