FOMRAT SQL function

1. Usage of FORMAT to format order dates.

SQL Server Query 1

            
SELECT TOP 10 OrderID, OrderDate, /* Original date*/   

	FORMAT(OrderDate, 'd') AS ShortDate_DefaultCulture, /* Short date pattern based on session culture */   

	FORMAT(OrderDate, 'd', 'en-US') AS ShortDate_US, /* Short date pattern (US) */   

	FORMAT(OrderDate, 'd', 'en-GB') AS ShortDate_UK, /* Short date pattern (UK) */   

	FORMAT(OrderDate, 'D', 'en-US') AS LongDate_US, /* Long date pattern (US) */   

	FORMAT(OrderDate, 'D', 'de-DE') AS LongDate_German, /* Long date pattern (German) */   

	FORMAT(OrderDate, 'yyyy-MM-dd') AS ISO_Format, /* Custom ISO-like format */   

	FORMAT(OrderDate, 'dd/MMM/yyyy', 'en-US') AS Custom_Format_US, /* Custom format (US English) */   

	FORMAT(OrderDate, 'MMMM dd, yyyy') AS Full_Month_Name, /* Custom format with full month name */   

	FORMAT(OrderDate, 'yyyy-MM-dd HH:mm:ss') AS DateTime_24hr /* Date and 24-hour time */   

FROM Orders   

ORDER BY OrderDate DESC;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select().Top(10)  

    .Column("OrderID", "OrderID")  

    .Column("OrderDate", "OrderDate")  

    .Column(new FORMAT(new Column("OrderDate"), "d"), "ShortDate_DefaultCulture")  

    .Column(new FORMAT(new Column("OrderDate"), "d", "en-US"), "ShortDate_US")  

    .Column(new FORMAT(new Column("OrderDate"), "d", "en-gb"), "ShortDate_UK")  

    .Column(new FORMAT(new Column("OrderDate"), "D", "en-US"), "LongDate_US")  

    .Column(new FORMAT(new Column("OrderDate"), "D", "de-de"), "LongDate_German")  

    .Column(new FORMAT(new Column("OrderDate"), "yyyy_MM_dd"), "ISO_Format")  

    .Column(new FORMAT(new Column("OrderDate"), "dd_MM_yyyy"), "Custom_Format_US")  

    .Column(new FORMAT(new Column("OrderDate"), "MMM_dd_yyyy"), "Full_Month_Name")  

    .Column(new FORMAT(new Column("OrderDate"), "yyyy_MM_dd_HH_mm_ss"), "DateTime_24hr")  

    .From("Orders")  

    .OrderBy(new OrderBy().SetColumnDescending("OrderDate"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 OrderID AS OrderID,
              OrderDate AS OrderDate,
              FORMAT(OrderDate, @pMAIN_2507200130334851080) AS ShortDate_DefaultCulture,
              FORMAT(OrderDate, @pMAIN_2507200130334851081, @pMAIN_2507200130334851082) AS ShortDate_US,
              FORMAT(OrderDate, @pMAIN_2507200130334851083, @pMAIN_2507200130334851084) AS ShortDate_UK,
              FORMAT(OrderDate, @pMAIN_2507200130334851085, @pMAIN_2507200130334851086) AS LongDate_US,
              FORMAT(OrderDate, @pMAIN_2507200130334851087, @pMAIN_2507200130334851088) AS LongDate_German,
              FORMAT(OrderDate, @pMAIN_2507200130334851089) AS ISO_Format,
              FORMAT(OrderDate, @pMAIN_250720013033485108_10) AS Custom_Format_US,
              FORMAT(OrderDate, @pMAIN_250720013033485108_11) AS Full_Month_Name,
              FORMAT(OrderDate, @pMAIN_250720013033485108_12) AS DateTime_24hr
FROM Orders
ORDER BY OrderDate DESC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of FORMAT to show different currencies.

SQL Server Query 2

            
SELECT TOP 15  

    od.OrderID,  

    p.ProductName,  

    od.UnitPrice AS OriginalUnitPrice,  

    od.Quantity,  

    (od.UnitPrice * od.Quantity * (1 - od.Discount)) AS CalculatedTotalPrice,  

    /* Formatting UnitPrice as Currency */  

    FORMAT(od.UnitPrice, 'C') AS Currency_DefaultCulture, /* Currency based on session culture */  

    FORMAT(od.UnitPrice, 'C', 'en-US') AS Currency_USD, /* US Dollar format */  

    FORMAT(od.UnitPrice, 'C', 'de-DE') AS Currency_EUR, /* Euro format (German culture) */  

    FORMAT(od.UnitPrice, 'C', 'ja-JP') AS Currency_JPY, /* Yen format (Japanese culture) */  

    FORMAT(od.UnitPrice, 'C', 'en-IN') AS Currency_INR, /* Indian Rupee format */  

    /* Formatting Calculated Total Price as Number */  

    FORMAT((od.UnitPrice * od.Quantity * (1 - od.Discount)), 'N2', 'en-US') AS TotalPrice_N2_US, /* Number with 2 decimal places (US) */  

    /* Formatting Discount as Percentage */  

    FORMAT(od.Discount, 'P1') AS Discount_Percentage /* Percentage format with 1 decimal place */  

FROM [Order Details] od  

JOIN Products p ON od.ProductID = p.ProductID  

WHERE od.Discount > 0 /* Let's find orders with discounts */  

ORDER BY  

    CalculatedTotalPrice DESC;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select().Top(15)  

    .Column("od.OrderID", "OrderID")  

    .Column("p.ProductName", "ProductName")  

    .Column("od.UnitPrice", "OriginalUnitPrice")  

    .Column("od.Quantity", "Quantity")  

    .Column(new ColumnArithmatic().StartBracket().Column("od.UnitPrice")  

        .MULTIPLY().Column("od.Quantity").MULTIPLY().StartBracket()  

        .Value(1).SUBTRACT().Column("od.Discount").EndBracket().EndBracket(), "CalculatedTotalPrice")  

    .Column(new FORMAT(new Column("od.UnitPrice"), "c"), "Currency_DefaultCulture")  

    .Column(new FORMAT(new Column("od.UnitPrice"), "c", "en-US"), "Currency_USD")  

    .Column(new FORMAT(new Column("od.UnitPrice"), "c", "de-de"), "Currency_EUR")  

    .Column(new FORMAT(new Column("od.UnitPrice"), "c", "ja-JP"), "Currency_JPY")  

    .Column(new FORMAT(new Column("od.UnitPrice"), "c", "hi-in"), "Currency_INR")  

    .Column(new FORMAT(new ColumnArithmatic()  

        .StartBracket()  

            .Column("od.UnitPrice").MULTIPLY().Column("od.Quantity").MULTIPLY()  

            .StartBracket()  

                .Value(1).SUBTRACT().Column("od.Discount")  

            .EndBracket()  

        .EndBracket(), "N2", "en-US"), "TotalPrice_N2_US")  

    .Column(new FORMAT(new Column("od.Discount"), "P1"), "Discount_Percentage")  

    .From("[Order Details]", "od")  

    .Join(new List<IJoin>()  

    {  

        new Join(SQLJoin.INNERJOIN).TableName(new Table("Products", "p"))  

            .On(new Column("od.ProductID"), SQLComparisonOperators.EQUALE_TO, new Column("p.ProductID"))  

    })  

    .Where(new Where(new Column("od.Discount"), SQLComparisonOperators.GREATER_THAN, 0))  

    .OrderBy(new OrderBy().SetColumnDescending("CalculatedTotalPrice"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 15 od.OrderID AS OrderID,
              p.ProductName AS ProductName,
              od.UnitPrice AS OriginalUnitPrice,
              od.Quantity AS Quantity,
              (od.UnitPrice * od.Quantity * (@pMAIN_2507200130334989310 - od.Discount)) AS CalculatedTotalPrice,
              FORMAT(od.UnitPrice, @pMAIN_2507200130334989311) AS Currency_DefaultCulture,
              FORMAT(od.UnitPrice, @pMAIN_2507200130334989312, @pMAIN_2507200130334989313) AS Currency_USD,
              FORMAT(od.UnitPrice, @pMAIN_2507200130334989314, @pMAIN_2507200130334989315) AS Currency_EUR,
              FORMAT(od.UnitPrice, @pMAIN_2507200130334989316, @pMAIN_2507200130334989317) AS Currency_JPY,
              FORMAT(od.UnitPrice, @pMAIN_2507200130334989318, @pMAIN_2507200130334989319) AS Currency_INR,
              FORMAT((od.UnitPrice * od.Quantity * (@pMAIN_250720013033498931_10 - od.Discount)), @pMAIN_250720013033498931_11, @pMAIN_250720013033498931_12) AS TotalPrice_N2_US,
              FORMAT(od.Discount, @pMAIN_250720013033498931_13) AS Discount_Percentage
FROM [Order Details] AS od
     INNER JOIN
     Products AS p
     ON od.ProductID = p.ProductID
WHERE od.Discount > @pMAIN_250720013033498931_14
ORDER BY CalculatedTotalPrice DESC;


            
        

Parameters (If used)

Query Results 2: