Need Sql

Connectivity

Need Sql

I am trying this tera data Sql this is for curent month date.

select cast(current_date as date) - (extract (day from cast(current_date as date)) - 1) + interval '1' month - 1

I need tera data sql for Last month begin date and last month end date. I searched various forums but could not get the answer. Any suggesstions please.

13 REPLIES
N/A

Re: Need Sql

first day current month: current_date - (extract(day from current_date)-1)

last day current month: add_months(current_date - (extract(day from current_date)-1),1)-1

last day last month: current_date - extract(day from current_date)

first day last_month: add_months(current_date - (extract(day from current_date)-1),-1)

Dieter

Re: Need Sql

Thanks for the reply.

I was trying this sql as posted by you.. and getting the below error.

first day last_month: add_months(current_date - (extract(day from current_date)-1),-1)

---------------------------

Oracle BI Administration Tool

---------------------------

[NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.

[nQSError: 43113] Message returned from OBIS.

[nQSError: 43093] An error occurred while processing the EXECUTE PHYSICAL statement.

[nQSError: 16001] ODBC error state: 37000 code: -3706 message: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between the beginning of the request and the 'first' keyword. .

[nQSError: 16014] SQL statement preparation failed.

---------------------------

OK  

---------------------------

N/A

Re: Need Sql

I assumed you know that a select starts with the SELECT keyword.

SELECT current_date - (extract(day from current_date)-1)

Dieter

Re: Need Sql

Yes I have added SELECT before the statement. But still getting the same error. I know the Sql of oracle. But don't have any idea of teradata sql. I need Sql for  Last month begin date and last month end date.

N/A

Re: Need Sql

What error?

"Syntax error: expected something between the beginning of the request and the 'first' keyword."?

SELECT

  add_months(current_date - (extract(day from current_date)-1),-1),

  current_date - extract(day from current_date);

will return last month begin date and last month end date

There isn't much difference between basic Oracle/Teradata syntax.

Dieter

Re: Need Sql

Thanks for the update. It is working.

What about the sql for current year and previous year.

N/A

Re: Need Sql

  ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), -EXTRACT(MONTH FROM CURRENT_DATE)+1),

  ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1), 12-EXTRACT(MONTH FROM CURRENT_DATE)+1)-1

Dieter

N/A

Re: Need Sql

And if you only want the year

CURRENT_DATE / 10000 + 1900

CURRENT_DATE / 10000 + 1900 -1

Re: Need Sql

Hi Team,

               We are migrating some of the queries from oracle to Teradata.

Almost we are completed still some the queries having weird behaving, Please see below mentioned issue which we are facing currently

Kindly suggested me how we can replicate same query .

Teradata

SELECT ADD_MONTHS(DATE-1,-3)

Result :- 2012-11-28 

Oracle

select add_months(sysdate-1,-3)

from dual 

Result :-2012-11-30