-- CAST: SQL Standard (works across all engines)

SELECT

CAST('123' AS INTEGER) AS int_value,

CAST(123.456 AS DECIMAL(10,2)) AS rounded_price,

CAST('2024-01-15' AS DATE) AS order_date,

CAST(CURRENT_TIMESTAMP AS DATE) AS today;

-- PostgreSQL :: shorthand

SELECT

'123'::INTEGER AS int_value,

'2024-01-15'::DATE AS parsed_date,

employee_id::TEXT AS id_string

FROM employees;

-- TRY_CAST: Safe conversion (SQL Server / Snowflake)

SELECT

TRY_CAST('123' AS INTEGER) AS valid_int, -- Returns 123

TRY_CAST('abc' AS INTEGER) AS invalid_int, -- Returns NULL (no error)

TRY_CAST('2024-13-01' AS DATE) AS bad_date; -- Returns NULL

-- CONVERT: SQL Server with format styles

SELECT

CONVERT(VARCHAR(10), GETDATE(), 103) AS uk_date, -- DD/MM/YYYY

CONVERT(VARCHAR(10), GETDATE(), 120) AS iso_date, -- YYYY-MM-DD

CONVERT(DECIMAL(10,2), '123.456') AS truncated;

CAST(value AS type): SQL standard — portable across all engines

PostgreSQL shorthand: value::type (concise but non-portable)

SQL Server CONVERT(type, value, style): adds date format style codes (103=UK, 120=ISO)

TRY_CAST(value AS type): returns NULL on failure instead of error (SQL Server / Snowflake)

Type precedence: DECIMAL > FLOAT > BIGINT > INT > SMALLINT (higher wins in mixed expressions)

Implicit conversion: automatic when types mismatch in expressions — avoid in WHERE clauses

TIMESTAMPTZ, JSONB (binary, GIN-indexed), :: shorthand, TRY_CAST via CASE/NULLIF pattern; BOOLEAN native; array types; enum types

JSON validated on insert, JSON_EXTRACT / -> shorthand; TINYINT(1) for boolean; CONVERT_TZ for timezone; DATETIME has no timezone awareness

CONVERT with style codes for date formatting; TRY_CAST / TRY_CONVERT native; BIT for boolean; NVARCHAR(MAX) + JSON functions (native JSON type in 2025 preview)

TO_NUMBER / TO_DATE / TO_CHAR for conversions; VARCHAR2 preferred over VARCHAR; TIMESTAMP WITH TIME ZONE; JSON type in 21c+; no native BOOLEAN in SQL (use CHAR(1))

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value