CAST SQL function

1. Usage of CAST to show details of order date.

SQL Server Query 1

            
SELECT OrderID AS OrderID, OrderDate AS OrderDate,   

	YEAR(OrderDate) AS OrderYear,   

	MONTH(OrderDate) AS OrderMonth,   

	DATENAME(month, OrderDate) AS OrderMonthName,   

	ShippedDate AS ShippedDate,   

	YEAR(ShippedDate) AS ShipYear,   

	MONTH(ShippedDate) AS ShipMonthNumber,   

	DATENAME(month, ShippedDate) AS ShipMonthName,   

	CONCAT(  

		CAST(YEAR(OrderDate) AS VARCHAR(4)), '-',   

		RIGHT(CONCAT(0, CAST(MONTH(OrderDate) AS VARCHAR(2))), 2), '-',   

		RIGHT(CONCAT(0, CAST(DAY(OrderDate) AS VARCHAR(2))), 2)  

	) AS FormattedOrderDate,   

	CONCAT(  

		CAST(YEAR(ShippedDate) AS VARCHAR(4)), '-',   

		RIGHT(CONCAT(0, CAST(MONTH(ShippedDate) AS VARCHAR(2))), 2), '-',   

		RIGHT(CONCAT(0, CAST(DAY(ShippedDate) AS VARCHAR(2))), 2)  

	) AS FormattedShippedDate,   

	CONCAT(CAST(DATEDIFF(day, OrderDate, ShippedDate) AS VARCHAR(10)), ' days') AS DaysToShip   

FROM Orders    

WHERE OrderID IN (10248, 10249, 10250, 10251, 10252)    

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Column("OrderID", "OrderID")  

    .Column("OrderDate", "OrderDate")  

    .Column(new YEAR(new Column("OrderDate")), "OrderYear")  

    .Column(new MONTH(new Column("OrderDate")), "OrderMonth")  

    .Column(new DATENAME(SqlDateInterval.month, new Column("OrderDate")), "OrderMonthName")  

    .Column("ShippedDate", "ShippedDate")  

    .Column(new YEAR(new Column("ShippedDate")), "ShipYear")  

    .Column(new MONTH(new Column("ShippedDate")), "ShipMonthNumber")  

    .Column(new DATENAME(SqlDateInterval.month, new Column("ShippedDate")), "ShipMonthName")  

    .Column(new CONCAT(  

        new CAST(new YEAR(new Column("OrderDate")), SqlDataType.VARCHAR, "4"), "-",  

        new RIGHT(new CONCAT("0", new CAST(new MONTH(new Column("OrderDate")), SqlDataType.VARCHAR, "2")), 2), "-",  

        new RIGHT(new CONCAT("0", new CAST(new DAY(new Column("OrderDate")), SqlDataType.VARCHAR, "2")), 2)  

        ), "FormattedOrderDate")  

    .Column(new CONCAT(  

            new CAST(new YEAR(new Column("ShippedDate")), SqlDataType.VARCHAR, "4"), "-",  

            new RIGHT(new CONCAT("0", new CAST(new MONTH(new Column("ShippedDate")), SqlDataType.VARCHAR, "2")), 2), "-",  

            new RIGHT(new CONCAT("0", new CAST(new DAY(new Column("ShippedDate")), SqlDataType.VARCHAR, "2")), 2)  

        )  

    , "FormattedShippedDate")  

    .Column(new CONCAT(  

            new CAST(new DATEDIFF(SqlDateInterval.day, new Column("OrderDate"), new Column("ShippedDate")), SqlDataType.VARCHAR, "10"),  

            " ", "days"  

        )  

    , "DaysToShip")  

    .From("Orders")  

    .Where(new Where(new IN(new Column("OrderID"), 10248, 10249, 10250, 10251, 10252)))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OrderID AS OrderID,
       OrderDate AS OrderDate,
       YEAR(OrderDate) AS OrderYear,
       MONTH(OrderDate) AS OrderMonth,
       DATENAME(month, OrderDate) AS OrderMonthName,
       ShippedDate AS ShippedDate,
       YEAR(ShippedDate) AS ShipYear,
       MONTH(ShippedDate) AS ShipMonthNumber,
       DATENAME(month, ShippedDate) AS ShipMonthName,
       CONCAT(CAST (YEAR(OrderDate) AS VARCHAR (4)), @pMAIN_2507192019002938930, RIGHT(CONCAT(@pMAIN_2507192019002938931, CAST (MONTH(OrderDate) AS VARCHAR (2))), @pMAIN_2507192019002938932), @pMAIN_2507192019002938933, RIGHT(CONCAT(@pMAIN_2507192019002938934, CAST (DAY(OrderDate) AS VARCHAR (2))), @pMAIN_2507192019002938935)) AS FormattedOrderDate,
       CONCAT(CAST (YEAR(ShippedDate) AS VARCHAR (4)), @pMAIN_2507192019002938936, RIGHT(CONCAT(@pMAIN_2507192019002938937, CAST (MONTH(ShippedDate) AS VARCHAR (2))), @pMAIN_2507192019002938938), @pMAIN_2507192019002938939, RIGHT(CONCAT(@pMAIN_250719201900293893_10, CAST (DAY(ShippedDate) AS VARCHAR (2))), @pMAIN_250719201900293893_11)) AS FormattedShippedDate,
       CONCAT(CAST (DATEDIFF(day, OrderDate, ShippedDate) AS VARCHAR (10)), @pMAIN_250719201900293893_12, @pMAIN_250719201900293893_13) AS DaysToShip
FROM Orders
WHERE OrderID IN (@pMAIN_250719201900293893_14, @pMAIN_250719201900293893_15, @pMAIN_250719201900293893_16, @pMAIN_250719201900293893_17, @pMAIN_250719201900293893_18);


            
        

Parameters (If used)

Query Results 1:

2. Usage of CAST to show formatted unit price.

SQL Server Query 2

            
SELECT  

    ProductID,  

    ProductName,  

    UnitPrice,  

    -- CAST UnitPrice to integer  

    CAST(UnitPrice AS INT) AS UnitPriceInteger,  

    -- CAST UnitPrice to string with 2 decimal places  

    CAST(FORMAT(UnitPrice, 'N2') AS VARCHAR(20)) AS UnitPriceFormatted  

FROM Products  

WHERE ProductID IN (1, 2, 3);  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

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

    .Column(new CAST(new Column("UnitPrice"), SqlDataType.INT), "UnitPriceInteger")  

    .Column(new CAST(new FORMAT(new Column("UnitPrice"), "N2"), SqlDataType.VARCHAR, "20"), "UnitPriceFormatted")  

    .From("Products")  

    .Where(new Where(new IN(new Column("ProductID"), 1, 2, 3)))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT ProductID,
       ProductName,
       UnitPrice,
       CAST (UnitPrice AS INT) AS UnitPriceInteger,
       CAST (FORMAT(UnitPrice, @pMAIN_2507192019003069660) AS VARCHAR (20)) AS UnitPriceFormatted
FROM Products
WHERE ProductID IN (@pMAIN_2507192019003069661, @pMAIN_2507192019003069662, @pMAIN_2507192019003069663);


            
        

Parameters (If used)

Query Results 2:

3. Usage of CAST to show formatted employee info.

SQL Server Query 3

            
SELECT  

    EmployeeID,  

    FirstName,  

    LastName,  

    -- Concatenate EmployeeID, FirstName, and LastName  

    'Employee ID: ' + CAST(EmployeeID AS VARCHAR(10)) + ', Name: ' + FirstName + ' ' + LastName AS EmployeeInfo  

FROM Employees  

WHERE EmployeeID IN (1, 2, 3);  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Columns("EmployeeID", "FirstName", "LastName")  

    .Column(new CONCAT(  

            "Employee ID: ",  

            new CAST(new Column("EmployeeID"), SqlDataType.VARCHAR, "10"),  

            ", Name: ",  

            new Column("FirstName"),  

            " ",  

            new Column("LastName")  

        ), "EmployeeInfo")  

    .From("Employees")  

    .Where(new Where(new IN(new Column("EmployeeID"), 1, 2, 3)))  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT EmployeeID,
       FirstName,
       LastName,
       CONCAT(@pMAIN_2507192019004447630, CAST (EmployeeID AS VARCHAR (10)), @pMAIN_2507192019004447631, FirstName, @pMAIN_2507192019004447632, LastName) AS EmployeeInfo
FROM Employees
WHERE EmployeeID IN (@pMAIN_2507192019004447633, @pMAIN_2507192019004447634, @pMAIN_2507192019004447635);


            
        

Parameters (If used)

Query Results 3: