TERADATA: LPAD Equivalent

LPAD

Teradata Equivalent for LPAD Function

SELECT '1'                                                                    AS CHAR_NUM

      ,SUBSTRING('000000' FROM 1 FOR 6-CHARS(TRIM(CHAR_NUM)))||TRIM(CHAR_NUM) AS LPAD_NUM

;

CHAR_NUM	LPAD_NUM
1	000001

TERADATA: Help Commands

HELP Commands

Teradata HELP commands

Teradata provides one of the user friendly featured like HELP commands.

HELP SESSION;   

This command is used to display the user name, account name, logon date and time, current database name, collation code set and character set being used and also , transaction semantics, time zone and character set data.

HELP DATABASE <database-name> ;   

This command is used to display the names of all the tables (T), views (V), macros (M), and triggers (G) stored in a database and table comments

HELP USER <user-name> ;   

This command is used to display the names of all the tables (T), views (V), macros (M), and triggers (G) stored in a user area and table comments

HELP TABLE <table-name> ;   

This command is used to display the column names, type identifier, and any user written comments on the columns within a table.

HELP VOLATILE TABLE ;    

This command is used to display the names of all Volatile temporary tables active for the current  user session.

HELP VIEW <view-name> ;   

This command is used to display the column names, type identifier, and comments on the columns within a view.

HELP MACRO <macro-name> ;   

This command is used to display the characteristics of parameters passed to it at execution time.

HELP TRIGGER <trigger-name> ;   

This command is used to display details created for a trigger, like action time and sequence.

HELP PROCEDURE <procedure-name> ;   

This command is used to display the characteristics of parameters passed to it at execution time.

HELP COLUMN <table-name>.*  OR  HELP COLUMN <view-name>.*  OR HELP COLUMN <table-name>.<column-name>, .…;   

This command is used to display detail data describing the column level characteristics.

HELP INDEX <table-name> ;   

This command is used to display the indexes and their characteristics like unique or non-unique and the column or columns involved in the index. This information  is used by the Optimizer to create a plan for SQL.

HELP STATISTICS <table-name> ;   

This command is used to display values associated with the data demographics collected on the table. This information is used by the Optimizer to create a plan for SQL.

HELP STATS <table-name><column-name> ;   

This command is used to display values associated with the data demographics collected on the table. This information is used by the Optimizer to create a plan for SQL.

HELP CONSTRAINT <table-name>.<constraint-name> ;   

This command is used to display the checks to be made on the data when it is inserted or updated and the columns are involved.

HELP 'SQL';    

This command is used to display a list of all  available SQL commands and functions.

HELP 'SQL <command>';   

This command is used to display the basic syntax and options for the SQL command used in place of the <command>.

HELP 'SPL';    

This command is used to display a list of available SPL commands.

HELP 'SPL <command>';   

This command is used to display the basic syntax and options for the SPL command used in place of the <command>.

TERADATA: Date and Time Functions

Date and Time functions

Monday, September 22, 2014

7:51 AM

FIRST and LAST Day of Month

SELECT CURRENT_DATE;

SELECT (CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1)                          AS "1st Day Cur Mo"

      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), -1)          AS "1st Day Prev1 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), -2)          AS "1st Day Prev2 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 1)           AS "1st Day Next1 Mo"
      ,ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 2)           AS "1st Day Next2 Mo"
      ,LAST_DAY(CURRENT_DATE)                                                     AS "Last Day Cur Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-1)) AS "Last Day Prev1 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1),-2)) AS "Last Day Prev2 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 1)) AS "Last Day Next1 Mo"
      ,LAST_DAY(ADD_MONTHS((CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)+1), 2)) AS "Last Day Next2 Mo"
;

Basic Date Functions

Display current date.
DATE or CURRENT_DATE

Display current time.
TIME or CURRENT_TIME

Display combination of both date and time.
CURRENT_TIMESTAMP

Add 3 months to the current date.
ADD_MONTHS(date,3)

Subtract 2 months from the current date.
ADD_MONTHS(date, -2)

EXTRACT Function:
EXTRACT(Year FROM Date)
EXTRACT(Month FROM Date)
EXTRACT(Day FROM Date)
EXTRACT(Hour FROM Time)
EXTRACT(Minute FROM Time)
EXTRACT(Second FROM Time)

Count by YYYY-MM:

SELECT CAST(EXTRACT(Year FROM READING_DATE_TS) AS CHAR(4))||'-'||
       SUBSTRING('00' FROM 1 FOR 2-CHARS(TRIM(EXTRACT(Month FROM READING_DATE_TS))))||
       TRIM(EXTRACT(Month FROM READING_DATE_TS)) AS YEAR_MTH
      ,COUNT(1) AS TOTAL
  FROM TABLE
GROUP BY 1
ORDER BY 1 DESC;

 

TERADATA: Data Types

Data Types

Thursday, November 14, 2013

2:02 PM

Value	ColumnType	Description
++		TD_ANYTYPE
A1	ARRAY	One dimensional ARRAY data type
AT	TIME	ANSI Time
BF	BYTE	BYTE Fixed
BO	BLOB	Byte Large Object
BV	VARBYTE	Byte Varying
CF	CHAR	Character Fixed
CO	CLOB	Character Large Object
CV	VARCHAR	Character Varying Latin
D	DECIMAL	Decimal
DA	DATE	Date
DH	INTERVAL DAY TO HOUR	Interval Day To Hour
DM	INTERVAL DAY TO MINUTE	Interval Day To Minute
DS	INTERVAL DAY TO SECOND	Interval Day To Second
DY	INTERVAL DAY	Interval Day
F	FLOAT	Float
GF	GRAPHIC
GV	VARGRAPHIC
HM	INTERVAL HOUR TO MINUTE	Interval Hour To Minute
HR	INTERVAL HOUR	Interval Hour
HS	INTERVAL HOUR TO SECOND	Interval Hour To Second
I1	BYTEINT	1 Byte Integer
I2	SMALLINT	2 Byte Integer
I8	BIGINT	8 Byte Integer
I	INTEGER	4 Byte integer
MI	INTERVAL MINUTE	Interval Minute
MO	INTERVAL MONTH	Interval Month
MS	INTERVAL MINUTE TO SECOND	Interval Minute To Second
N	NUMBER	Number
PD		PERIOD(DATE)
PM		PERIOD(TIMESTAMP(n) WITH TIMEZONE)
PS		PERIOD(TIMESTAMP (n))
PT		PERIOD(TIME(n))
PZ		PERIOD (TIME(n) WITH TIME ZONE)
SC	INTERVAL SECOND	Interval Second
SZ	TIMESTAMP WITH TIME ZONE	Timestamp With Time Zone
TS	TIMESTAMP	Timestamp
TZ	TIME WITH TIME ZONE	ANSI Time With Time Zone
UF		Character Fixed Unicode
UT	UDT Type	UDT Type
UV		Character Varying Unicode
YI		Year Interval
YM	INTERVAL YEARTO MONTH	Interval Year To Month
YR	INTERVAL YEAR	Year