MySQL script with dateadd in Teradata

Database
Fan

MySQL script with dateadd in Teradata

Hi, I have the below scrip in MySQL and for the last few days I was trying to change it to Teradata.

SyntaxEditor Code Snippet

Select CAST (dateadd (day, 0, getdate()) AS DATE) AS dt UNION ALL
Select CAST (dateadd(day,1, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,2, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,3, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,4, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,5, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,6, getdate()) AS DATE) UNION ALL
Select CAST (dateadd(day,7, getdate()) AS DATE) 

 Can anyone help please?


Accepted Solutions
Teradata Employee

Re: MySQL script with dateadd in Teradata

Hi tomgag,

 

There's many ways to achieve this, but I'll give you the usefull one here.

 Teradata has a build-in calendar table :

select calendar_date as dt
  from sys_calendar.calendar
 where calendar_date between current_date and current_date + 7;

 Btw, it looks more SQL-Server than MySQL.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: MySQL script with dateadd in Teradata

Hi tomgag,

 

There's many ways to achieve this, but I'll give you the usefull one here.

 Teradata has a build-in calendar table :

select calendar_date as dt
  from sys_calendar.calendar
 where calendar_date between current_date and current_date + 7;

 Btw, it looks more SQL-Server than MySQL.

Teradata Employee

Re: MySQL script with dateadd in Teradata

You should also know that in general date arithmetic is very easy in Teradata: just use the arithmetic operators + and -.  For example, Select DATE+1 or Select CURRENT_DATE-7.