could some one help me to convert below sql server to teradata.
CASE WHEN DATE_ID=CONVERT(DATETIME, CONVERT(DATE, GETDATE())) THEN CONVERT(DATETIME, CONVERT(DATE, GETDATE())) ELSE
DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, A.DATE_ID), DATEDIFF(dd, 0, A.DATE_ID)))
It depends on whether the Teradata implementation of "DATE_ID" is a DATE data type (which would seem to be appropriate, given this logic) or a TIMESTAMP data type, and which day is the first day of the week in your SQL Server configuration. Do you have some examples of input & output on SQL Server?
Date arithmetic is easy in Teradata SQL - just use + or -, and today's date is simply DATE or CURRENT_DATE. So tomorrow is DATE+1 or CURRENT_DATE+1. Also take a look at the view Sys_Calendar.CALENDAR to see what kind of calendar information you can get by just joining to it. Run this query and study the output to see what you can do with some of the columns in this view:
Select calendar_date, day_of_week, day_of_year, day_of_calendar, weekday_of_month, week_of_month, week_of_year, week_of_calendar
where calendar_date between date'2018-01-01' and date'2018-03-31'
order by 1;
For example, if day_of_week is 7 (Saturday), this is a week-end date. Also check out the BusinessCalendar and BusinessCalendarPatterns in Sys_Calendar. Besides that, there are also calendar functions - see the SQL Suntions manual at info.teradata.com for more.
When you always want to get the next Saturday/Sunday/whatever you can simply use NEXT_DAY, e.g.
SELECT NEXT_DAY(date_id, 'Sun') -- next sunday > date_id SELECT NEXT_DAY(date_id-1, 'Sun') -- next sunday >= date_id
If you want the next Sunday four weeks ago
SELECT NEXT_DAY(date_id-4*7, 'Sun')
If date_id is a Timestamp you need to Cast it as a Date first.