Overview

CONVERT is SQL Server specific.
CAST is ANSI.

SELECT [Name], [ListPrice]
FROM [Production].[Product]
WHERE CONVERT(INT, [ListPrice]) LIKE '3%';
-- CAST([ListPrice] AS INT) LIKE '3%'

Date/Time Conversion

DECLARE @Date DATETIME = '2000-01-01 13:30:30'
SELECT
	@Date 'Original',	
	CONVERT(VARCHAR, @Date, 101) 'US Format', -- mm/dd/yyyy
	CONVERT(VARCHAR, @Date, 23) 'ISO 8601 Date', -- yyyy-mm-dd
	CONVERT(VARCHAR, @Date, 112) 'ISO 8601 Date, No Dash', -- yyyymmdd
	CONVERT(VARCHAR, @Date, 126) 'ISO 8601 DateTime' -- yyyy-mm-ddThh:mi:ss.mmm
	CONVERT(VARCHAR, @Date, 127) 'ISO 8601 DateTime & Time Zone Z.' -- yyyy-mm-ddThh:mi:ss.mmmZ

Binary Conversion

Because Unicode data always uses an even number of bytes, use caution when converting binary/varbinary to or from Unicode data types.
- For example, the following conversion does not return a hexadecimal value of 41; it returns 4100:

SELECT 
	CONVERT(VARBINARY, 'Test', 0), -- 0x54657374
	CONVERT(VARCHAR, 0x54657374, 0), -- Test
	CONVERT(NVARCHAR, 0x41), -- A
	CONVERT(VARBINARY, 'A'), -- 0x41
	CONVERT(VARBINARY, CONVERT(NVARCHAR, 0x41)) -- 0x4100

Explicit vs Implicit Conversions

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.
When you convert between datetimeoffset and the character types char, varchar, nchar, and nvarchar the converted time zone offset part should always be double digits for both HH and MM for example, -08:00.