How to get the current timestamp in Teradata SQL

 

How to select current timestamp in Teradata SQL just like we use "select date" or 

"select current_date" to get the current date

There are two possible ways to get the desired results:

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0));

AND

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Both the above queries will display the same results in Teradata sql.

Read more »

Posted in: How to,SQL,TERADATA

Error Handling in BTEQ Teradata?

Error Handling in BTEQ Teradata?

BTEQ Return Codes:
Bteq return codes are the two digit values that BTEQ returns to the client operating system as a result of any error code occured in BTEQ session. Possible BTEQ return codes are as given below

Return Code Description
00 Job completed with no errors.
02 User alert to log on to the Teradata Database.
04 Warning error.
08 User error.
12 Severe internal error

The return code is decided by the Error messages that BTEQ receives from the Teradata database. Different Teradata database error codes are assigned a specific return code value.
The below table will give what different return codes are returned by BTEQ for different Error codes it receives from Teradata database.

For an Example : If you issue a SQL statement in BTEQ session to create a table which is alrady there, then Teradata database will return an error code of 3803 to the BTEQ session and in turn in BTEQ will send a return code of 04 to the client operating system where you have intiated the BTEQ session.

Return Code = 04 ( BTEQ returns a return code of 04 for the following Teradata error codes )
2580 - Mload not active on table %TVMID.
2667 - Statistics cannot be collected on an empty table. 
3534 - Index already exists.
3666 - This view has too many columns to store or retrieve comments.
3737 - Name is longer than 30 characters.
3747- No start-up string defined for this user.
3803 - Table “%VSTR” already exists.
3804 - View “%VSTR” already exists.
3805 - Macro “%VSTR” already exists.

A Sample Code Part Using BTEQ Conditional Logic:

The Bank offers a number of special services to its Million-Dollar customers.
-------------------------------------------------------------
DELETE FROM Million_Dollar_Customer ALL;
.IF ERRORCODE = 0 THEN .GOTO TableOK
CREATE TABLE Million_Dollar_Customer
(Account_Number INTEGER
,Customer_Last_Name VARCHAR(20)
,Customer_First_Name VARCHAR(15)
,Balance_Current DECIMAL(9,2));
.LABEL TableOK
INSERT INTO Million_Dollar_Customer
SELECT A.Account_Number
,C.Last_Name
,C.First_Name
,A.Balance_Current
FROM Accounts A INNER JOIN
Account_Customer AC INNER JOIN
Customer C
ON C.Customer_Number = AC.Customer_Number
ON A.Account_Number = AC.Account_Number
WHERE A.Balance_Current GT 1000000;
.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue
.QUIT
.LABEL Continue
------------------------------------------------------------

What it does???

- DELETE all rows from the Million_Dollar_Customer table.
- IF this results in an error (non-zero), THEN create the table, ELSE attempt to populate using INSERT/SELECT.
- IF some rows are inserted (ACTIVITYCOUNT>0) THEN arrange services, ELSE terminate the job.

Read more »

Posted in: ACTIVITY COUNT,blogger,blogging,blogspot,ERROR HANDLING,ERRORCODE=0,How to,SQL,TERADATA,TERADATA SQL,we-love-to-share.blogspot.com

how to cast date in MMYYYY format in SQL Teradata

how to cast date in YYYYMM format using SQL in Teradata

For odbc connections (e.g if you are using sql assistant/queryman) then use:
sel cast((current_Date (format 'yyyymmdd' )) as char(8));

For native connections (e.g if you are using bteq) then use:
sel current_date (format 'yyyymmdd');

however you may use below query(same as for sql assistant) in bteq also ...
sel cast((current_Date (format 'yyyymmdd' )) as char(8));

This is how you can cast date in different formats in sql teradata. Simple :-)

Read more »

Posted in: CASTING,DATE FORMAT,How to,SQL,TERADATA,TERADATA SQL,USING SQL