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:
- CAST is ANSI-SQL compliant; therefore, more apt to be used in other database implementation.
- There is no performance penalty using CAST.
- I think CAST is easier to read, and since it is part of the ANSI specification, your non-SQLServer DBA think so too!
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