TSQL CAST

INT to VARCHAR

DECLARE @v1 INT

SELECT @v1 = CAST(colValue AS INT) FROM tblTable WHERE colName = 'Name 1';

PRINT @v1

SET @Msg = 'Id ' + CAST(@ID as VARCHAR) + ' updated.';

DATETIME to DATE and TIME

SELECT 
	CAST(GETDATE() AS DATE) AS Col01,
	CAST(GETDATE() AS TIME(0)) AS Col02

Should I use CAST or Convert?

Unless you have some specific formatting requirements you’re trying to address during the conversion, I would stick with using the CAST function.  There are several reason I can think of:

1. CAST is ANSI-SQL compliant; therefore, more apt to be used in other database implementation.
2. There is no performance penalty using CAST.
3. I think CAST is easier to read, and since it is part of the ANSI specification, your non-SQLServer DBA think so too!

Note:

If you need to CAST a constant in the UPDATE statement, it will perform better by setting it to a variable and then use the variable in the UPDATE statement.

UPDATE Table01
   SET Field01 = Field02 * CAST(10000 AS BIGINT)      

DECLARE @bi10000 BIGINT = 10000;

UPDATE Table01
   SET Field01 = Field02 * @bi10000      

Sources:

https://www.essentialsql.com/what-is-the-difference-between-cast-and-convert/