CAST, CONVERT, and safe conversions

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Convert values deliberately and handle text that may not be convertible.

Run the example

SELECT TOP (3) EmployeeID,
       CAST(EmployeeID AS varchar(10)) AS EmployeeIDText,
       CONVERT(varchar(10), HireDate, 23) AS ISODate
FROM dbo.Employees
ORDER BY EmployeeID;

Read it step by step

1. CAST(EmployeeID AS varchar(10)) turns an int into text in the result.
2. CONVERT(varchar(10), HireDate, 23) produces a yyyy-mm-dd text value using SQL Server style 23.
3. ORDER BY EmployeeID makes the TOP (3) sample reproducible. Neither conversion changes the stored columns.

Check the result

ISODate is text in this result; converting for display does not change HireDate in the table.

Practice

For ProductID 1, try converting the text 42 and the text not-a-number to int. Show both values.

SELECT ProductID, TRY_CONVERT(int, N'42') AS ValidNumber,
       TRY_CONVERT(int, N'not-a-number') AS InvalidNumber
FROM dbo.Products
WHERE ProductID = 1;