DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, date_id), DATEDIFF(dd, 0, date_id))) to Teradata

Database
Tourist

DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, date_id), DATEDIFF(dd, 0, date_id))) to Teradata

Hi All,

 

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)))

END

 

Thanks,

Raja

3 REPLIES
Teradata Employee

Re: DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, date_id), DATEDIFF(dd, 0, date_id))) to Teradata

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?

Teradata Employee

Re: DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, date_id), DATEDIFF(dd, 0, date_id))) to Teradata

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
from Sys_Calendar.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.

Junior Contributor

Re: DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, date_id), DATEDIFF(dd, 0, date_id))) to Teradata

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.