All Forums Connectivity
ragavender 4 posts Joined 01/12
30 Jan 2012
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.

 

dnoeth 4628 posts Joined 11/04
30 Jan 2012

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

 

Dieter

ragavender 4 posts Joined 01/12
30 Jan 2012

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

dnoeth 4628 posts Joined 11/04
30 Jan 2012

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

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

Dieter

Dieter

ragavender 4 posts Joined 01/12
30 Jan 2012

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.

dnoeth 4628 posts Joined 11/04
30 Jan 2012

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

Dieter

ragavender 4 posts Joined 01/12
01 Feb 2012

Thanks for the update. It is working.

What about the sql for current year and previous year.

dnoeth 4628 posts Joined 11/04
01 Feb 2012

  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

Dieter

ulrich 816 posts Joined 09/09
02 Feb 2012

And if you only want the year

CURRENT_DATE / 10000 + 1900

CURRENT_DATE / 10000 + 1900 -1

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

28 Feb 2013

 
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

Thanx,
Mahesh

01 Mar 2013

 
BIG stuck up, almost we are facing same BIG issue in all reports whichever we were used by ADD_MONTHS function!
Please give me some suggestions for same, eagerly waiting for reply
Or Let me know do want me to raise incident to TD-corporation  ?

Thanx,
Mahesh

dnoeth 4628 posts Joined 11/04
01 Mar 2013

Hi Mahesh,
it's not a bug.
ADD_MONTHS is not a Standard SQL function, so there are no common rules how to implement it and Oracle uses a different rule than Teradata. Btw, both rules have some disadvantage :-)
Which release are you running?
In TD14 there's a built-in OADD_MONTHS, before there's a bunch of Oracle UDFs which include this and on 13.10 you could simply implement a SQL UDF:

REPLACE FUNCTION OADD_MONTHS
(
   cdate DATE, 
   incr INT
)
RETURNS DATE
SPECIFIC oadd_months_dt
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN 
CASE
   WHEN EXTRACT(MONTH FROM cdate) <> EXTRACT(MONTH FROM cdate+1)
   THEN ADD_MONTHS(cdate+1,incr)-1
   ELSE ADD_MONTHS(cdate,incr)
END
;

REPLACE FUNCTION OADD_MONTHS
(
   cdate TIMESTAMP(6), 
   incr INT
)
RETURNS DATE
SPECIFIC oadd_months_ts
RETURNS NULL ON NULL INPUT
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN OADD_MONTHS(CAST (cdate AS DATE), incr);
;

You have to change all occurence of ADD_MONTHS to OADD_MONTHS anyway, maybe it's already implemented.
Dieter
 
 

Dieter

01 Mar 2013

Thank you very much Dieter with your UDF's we are getting results same as ORACLE
But we are going with Teradata :)
because we have confirmed the logic required for our case which is same to Teradata.
 
Oracle ADD_MONTHS is not working as expected for '28 Feb 2013' so i think its bug in Oracle(
as for your suggestion ADD_MONTHS is not a Standard SQL function)
 
 

Thanx,
Mahesh

dnoeth 4628 posts Joined 11/04
01 Mar 2013

Hi Mahesh,
it's not a bug, it's different logic, and according to this logic both are correct:
Teradata returns the last day of a month only when this day doesn't exist in the resulting month.
Oracle always returns the last day of a month when the starting date is the last day of a month.
Both return some unexpected results in special cases, e.g. adding one month and then substracting one month:

SELECT DATE '2013-01-31' AS d, ADD_MONTHS(d,1) AS d2, ADD_MONTHS(d2,-1);
         d          d2  ADD_MONTHS(d2, -1)
----------  ----------  ------------------
2013-01-31  2013-02-28          2013-01-28

SELECT DATE '2013-01-30' AS d, OADD_MONTHS(d,1) AS d2, OADD_MONTHS(d2,-1);

         d          d2  OADD_MONTHS(d2,-1)
----------  ----------  ------------------
2013-01-30  2013-02-28          2013-01-31

When you search for it you'll find Oracle UDFs which mimic Teradata's verion of ADD_MONTHS, too :-)
Dieter

Dieter

You must sign in to leave a comment.