Type Conversion — CAST, TRY_CAST, CONVERT, ::

Four spellings convert a value to another SQL type. They differ in failure behaviour and in argument order, not in capability.

Form	Syntax	On invalid input
CAST	CAST(expr AS TYPE)	raises an error
CONVERT	CONVERT(expr, TYPE) or CONVERT(TYPE, expr)	raises an error
TRY_CAST (alias SAFE_CAST)	TRY_CAST(expr AS TYPE)	returns NULL
:: operator	expr::TYPE	raises an error

CONVERT accepts both argument orders — CONVERT(expr, TYPE) and the Transact-SQL CONVERT(TYPE, expr). SAFE_CAST is accepted as an alias and normalises to TRY_CAST.

SELECT CAST(hired_on AS DATE)         AS d FROM emp;
SELECT emp_no::BIGINT                 AS b FROM emp;
SELECT TRY_CAST(hired_on AS DATE)     AS d FROM emp;  -- NULL where unparseable
SELECT CONVERT(salary, DOUBLE)        AS s FROM emp;

Every statement needs a FROM clause — there is no bare SELECT <expression> form.

Target types

Category	Accepted target types
String	VARCHAR, STRING, CHAR
Integer	TINYINT, SMALLINT, INT, INTEGER, BIGINT
Floating point	DOUBLE, FLOAT, REAL
Boolean	BOOLEAN
Temporal	DATE, TIME, DATETIME, TIMESTAMP

Not cast targets

DECIMAL and NUMERIC are not supported as cast targets — cast to DOUBLE and round explicitly. TEXT, KEYWORD and BOOL are column types you declare in CREATE TABLE; as a cast target write VARCHAR or BOOLEAN instead.

Cast the input of an aggregate, not its result

A cast cannot be applied to the result of an aggregate. An aggregate must be the first function in a column’s chain, and a cast wrapping it would sit ahead of it:

-- Rejected: "Aggregation function must be the first function in the chain"
SELECT MAX(salary)::BIGINT           AS m FROM emp GROUP BY dept;
SELECT CAST(MAX(salary) AS BIGINT)   AS m FROM emp GROUP BY dept;

All four spellings behave the same way here. Cast the aggregate’s input instead — equivalent for every aggregate whose result type follows its argument (MIN, MAX, SUM, AVG, the STDDEV / VARIANCE family, the percentiles):

-- Both supported
SELECT MAX(salary::BIGINT)           AS m FROM emp GROUP BY dept;
SELECT MAX(CAST(salary AS BIGINT))   AS m FROM emp GROUP BY dept;

The same applies in HAVING: HAVING COUNT(*)::INT > 3 is rejected, and unnecessary — compare against a numeric literal directly.

Where casts are allowed

Anywhere an aggregate is not involved: the SELECT list, WHERE, ORDER BY, and inside another function.

SELECT YEAR(createdAt)::VARCHAR AS y FROM logs;

SELECT id
FROM logs
WHERE tries::INT > 3
ORDER BY tries::INT;

Behaviour notes

• CAST and CONVERT raise an error on an invalid conversion; TRY_CAST returns NULL, which makes it the right choice for parsing user-supplied or loosely-typed source fields.
• An explicit cast updates the type context for functions applied after it, so '125'::BIGINT + 1 is arithmetic rather than string concatenation.
• Casting is evaluated inside the generated Painless script, so a cast on a field participates in script-based filtering and sorting like any other expression.