TRY_CAST SQL function

1. Various usage of TRY_CAST.

SQL Server Query 1

            
SELECT TRY_CAST('21474836480' AS INT) AS SafeIntConversion,   

	TRY_CAST('21474836480' AS BIGINT) AS SafeBigIntConversion,  

	TRY_CAST('12345' AS MONEY) AS SafeMoneyResult,   

	CASE WHEN TRY_CAST('QWERTY' AS INT) IS NULL  

		THEN 'CastFailed' ELSE 'CastSucceeded'  END AS CaseResult,  

	TRY_CAST('ZXCVBNMM' AS INT) AS SafeIntResult,   

	TRY_CAST(112233.4455 AS INT) AS SafeOtherIntResult,  

	TRY_CAST(GETDATE() AS TIME) SafeTimeResult,   

	TRY_CAST(GETDATE() AS VARCHAR(22)) AS SafeDateTimeResult,   

	TRY_CAST('123.45' AS DECIMAL(6, 2)) AS SafeDecimalResult;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

.Select()  

    .Column(new TRY_CAST("21474836480", SqlDataType.INT), "SafeIntConversion")  

    .Column(new TRY_CAST("21474836480", SqlDataType.BIGINT), "SafeBigIntConversion")  

    .Column(new TRY_CAST("12345", SqlDataType.MONEY), "SafeMoneyResult")  

    .Column(new CASE().When(new IS_NULL(new TRY_CAST("QWERTY", SqlDataType.INT)))  

        .Then("CastFailed").Else("CastSucceeded"), "CaseResult")  

    .Column(new TRY_CAST("ZXCVBNMM", SqlDataType.INT), "SafeIntResult")  

    .Column(new TRY_CAST(112233.4455, SqlDataType.INT), "SafeOtherIntResult")  

    .Column(new TRY_CAST(new GETDATE(), SqlDataType.TIME), "SafeTimeResult")  

    .Column(new TRY_CAST(new GETDATE(), SqlDataType.VARCHAR, "22"),"SafeDateTimeResult")  

    .Column(new TRY_CAST(123.45, SqlDataType.DECIMAL, Tuple.Create(6, 2)), "SafeDecimalResult")  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TRY_CAST (@pMAIN_2507192027003037720 AS INT) AS SafeIntConversion,
       TRY_CAST (@pMAIN_2507192027003037721 AS BIGINT) AS SafeBigIntConversion,
       TRY_CAST (@pMAIN_2507192027003037722 AS MONEY) AS SafeMoneyResult,
       CASE WHEN TRY_CAST (@pMAIN_2507192027003037723 AS INT) IS NULL THEN @pMAIN_2507192027003037724 ELSE @pMAIN_2507192027003037725 END AS CaseResult,
       TRY_CAST (@pMAIN_2507192027003037726 AS INT) AS SafeIntResult,
       TRY_CAST (@pMAIN_2507192027003037727 AS INT) AS SafeOtherIntResult,
       TRY_CAST (GETDATE() AS TIME) AS SafeTimeResult,
       TRY_CAST (GETDATE() AS VARCHAR (22)) AS SafeDateTimeResult,
       TRY_CAST (@pMAIN_2507192027003037728 AS DECIMAL (6, 2)) AS SafeDecimalResult;


            
        

Parameters (If used)

Query Results 1:

2. Usage of TRY_CAST to show product price with tax.

SQL Server Query 2

            
SELECT  

    p.ProductName,  

    p.UnitPrice,  

    TRY_CAST(p.UnitPrice * 1.10 AS MONEY) AS PriceWithTax  

FROM Products p;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("p.ProductName","p.UnitPrice")  

    .Column(new TRY_CAST(new ColumnArithmatic("p.UnitPrice").MULTIPLY(1.10), SqlDataType.MONEY), "PriceWithTax")  

    .From("Products", "p")  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT p.ProductName,
       p.UnitPrice,
       TRY_CAST (p.UnitPrice * @pMAIN_2507192027003132930 AS MONEY) AS PriceWithTax
FROM Products AS p;


            
        

Parameters (If used)

Query Results 2: