SQL Reference - SPECIFICTYPE

SPECIFICTYPE

SPECIFICTYPE in SQL is a special data type that returns the specific name of the data type of the existing column in a table. It's used in certain database systems such as DB2, where it plays a crucial role within the Information Schema view. It provides valuable details about each column's specific data type inside a given database schema.

Example

SELECT CAST('01-Jan-2020' AS DATE) SPECIFICTYPE;

Output

    specifictype
-------------------
    2020-01-01
(1 row)

Explanation

The SPECIFICTYPE keyword used here in PostgreSQL is a part of the CAST function. This command takes the string ‘01-Jan-2020’ and converts, or “casts”, it into a DATE type. The result is outputted in the default date format ‘YYYY-MM-DD’.

Example

SELECT CAST('123' AS NUMBER(10,2)) FROM dual;

Output

123.00

Explanation

The above SQL command is an example of using the CAST function to convert a string ‘123’ into a NUMBER data type with a precision of 10 and a scale of 2. The data type NUMBER(10,2) signifies that the number can have a total of 10 digits including 2 digits after the decimal point. The FROM dual clause is used in Oracle to select a value without querying a table.