DATEADD SQL function

1. Usage of DATEADD for order processing time.

SQL Server Query 1

            
WITH OrderProcessed AS (  

    SELECT OrderID,  

        DATEADD(HOUR, 2, OrderDate) AS OrderProcessedDate  

    FROM Orders  

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

),  

OrderProcessing AS (  

    SELECT  

        Orders.OrderID,  

        OrderDate,  

        op.OrderProcessedDate,  

        CASE  

            WHEN op.OrderProcessedDate IS NULL THEN 'Not Processed Yet'  

            ELSE CONCAT(DATEDIFF(MINUTE, OrderDate, op.OrderProcessedDate), ' Minutes')  

        END AS ProcessingTime,  

        -- Calculate the scheduled processing time (e.g., 2 hours after OrderDate)  

        DATEADD(HOUR, 2, OrderDate) AS ScheduledProcessTime  

    FROM Orders LEFT JOIN OrderProcessed op ON op.OrderId = Orders.OrderID  

    WHERE Orders.OrderID IN (10248, 10249, 10250, 10251, 10252, 10253, 10254, 10255)   

)  

SELECT  

    OrderID,  

    OrderDate,  

    ScheduledProcessTime,  

    OrderProcessedDate,  

    ProcessingTime,  

    CASE  

        WHEN OrderProcessedDate IS NULL THEN 'Not Processed'  

        WHEN OrderProcessedDate <= DATEADD(MINUTE, -30, ScheduledProcessTime) THEN 'Early Processing'  

        WHEN OrderProcessedDate >= DATEADD(MINUTE, 30, ScheduledProcessTime) THEN 'Late Processing'  

        ELSE 'On-Time Processing'  

    END AS ProcessingStatus  

FROM OrderProcessing;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .WithCTETable(new Table("OrderProcessed"), new SqlQueryBuilder()  

        .Select().Column("OrderID", "OrderID")  

        .Column(new DATEADD(SqlDateInterval.hour, 2, new Column("OrderDate")), "OrderProcessedDate")  

        .From("Orders")  

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

    )  

    .WithCTETable(new Table("OrderProcessing"), new SqlQueryBuilder()  

        .Select()  

        .Columns("Orders.OrderID", "OrderDate", "op.OrderProcessedDate")  

        .Column(new CASE()  

            .When(new IS_NULL(new Column("op.OrderProcessedDate")))  

            .Then("Not Processed Yet")  

            .Else(new CONCAT(new DATEDIFF(SqlDateInterval.minute, new Column("OrderDate"), new Column("op.OrderProcessedDate"))  

                , " Minutes")), "ProcessingTime")  

        .Column(new DATEADD(SqlDateInterval.hour, 2, new Column("OrderDate")), "ScheduledProcessTime")  

        .From("Orders")  

        .Join(new List<IJoin>()  

        {  

            new LEFTJOIN().TableName(new Table("OrderProcessed","op"))  

                .On(new Column("op.OrderId"), SQLComparisonOperators.EQUALE_TO, new Column("Orders.OrderID"))  

        })  

        .Where(new Where(new IN(new Column("Orders.OrderID"), 10248, 10249, 10250, 10251, 10252, 10253, 10254, 10255)))  

    )  

    .Select().Columns("OrderID", "OrderDate", "ScheduledProcessTime", "OrderProcessedDate", "ProcessingTime")  

    .Column(new CASE()  

        .When(new IS_NULL(new Column("OrderProcessedDate")))  

        .Then("Not Processed")  

        .When(new Column("OrderProcessedDate").LessThanOrEqualeTo(  

            new DATEADD(SqlDateInterval.minute, -30, new Column("ScheduledProcessTime"))))  

        .Then("Early Processing")  

        .When(new Column("OrderProcessedDate").LessThanOrEqualeTo(  

            new DATEADD(SqlDateInterval.minute, 30, new Column("ScheduledProcessTime"))))  

        .Then("Late Processing")  

        .Else("On-Time Processing")  

    , "ProcessingStatus")  

    .From("OrderProcessing")  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH OrderProcessed
AS (SELECT OrderID AS OrderID,
           DATEADD(hour, @pMAIN_2507200159531550880, OrderDate) AS OrderProcessedDate
    FROM Orders
    WHERE OrderID IN (@pMAIN_2507200159531550881, @pMAIN_2507200159531550882, @pMAIN_2507200159531550883, @pMAIN_2507200159531550884)),
 OrderProcessing
AS (SELECT Orders.OrderID,
           OrderDate,
           op.OrderProcessedDate,
           CASE WHEN op.OrderProcessedDate IS NULL THEN @pMAIN_2507200159531550885 ELSE CONCAT(DATEDIFF(minute, OrderDate, op.OrderProcessedDate), @pMAIN_2507200159531550886) END AS ProcessingTime,
           DATEADD(hour, @pMAIN_2507200159531550887, OrderDate) AS ScheduledProcessTime
    FROM Orders
         LEFT OUTER JOIN
         OrderProcessed AS op
         ON op.OrderId = Orders.OrderID
    WHERE Orders.OrderID IN (@pMAIN_2507200159531550888, @pMAIN_2507200159531550889, @pMAIN_250720015953155088_10, @pMAIN_250720015953155088_11, @pMAIN_250720015953155088_12, @pMAIN_250720015953155088_13, @pMAIN_250720015953155088_14, @pMAIN_250720015953155088_15))
SELECT OrderID,
       OrderDate,
       ScheduledProcessTime,
       OrderProcessedDate,
       ProcessingTime,
       CASE WHEN OrderProcessedDate IS NULL THEN @pMAIN_250720015953155088_16 WHEN OrderProcessedDate <= DATEADD(minute, @pMAIN_250720015953155088_17, ScheduledProcessTime) THEN @pMAIN_250720015953155088_18 WHEN OrderProcessedDate <= DATEADD(minute, @pMAIN_250720015953155088_19, ScheduledProcessTime) THEN @pMAIN_250720015953155088_20 ELSE @pMAIN_250720015953155088_21 END AS ProcessingStatus
FROM OrderProcessing;


            
        

Parameters (If used)

Query Results 1: