DateAdd

2005
2008
2012
2014
2016
2017
2019
2022

2005, 2008, 2012, 2014, 2016, 2017, 2019, 2022

DateAdd Date and Time Function

Use the DateAdd function to add a number to a part of a date.

Syntax

db.fx.DateAdd({DATEPART}, {number}, {expression})

Arguments

DATEPART: – The part of `expression` to add `number`. `DATEPART` is an enumeration (in the `DbExpression.MsSql.Expression` namespace).

number: – The value to add.

expression: – The date value, where the part specified by `DATEPART`, that `number` is added.

Returns

DateTime or DateTime?

(based on nullability of `expression` and `number`)

Examples

Select Statement

Select the addition of 1 to the ship date of all products.

IEnumerable<DateTime?> result = db.SelectMany(
        db.fx.DateAdd(DateParts.Year, 1, dbo.Purchase.ShipDate)
    )
    .From(dbo.Purchase)
    .Execute();

Where Clause

Select all product ids where it took longer than 15 days from purchase to ship.

IEnumerable<int> result = db.SelectMany(
        dbo.Purchase.Id
    )
    .From(dbo.Purchase)
    .Where(db.fx.DateAdd(DateParts.Day, -15, dbo.Purchase.ShipDate) > dbo.Purchase.PurchaseDate)
    .Execute();

Order By Clause

Select all purchases ordered by the addition of 1 to ship date. (this example shows order, but the actual logic isn't any different than simply ordering by ship date).

IEnumerable<Purchase> result = db.SelectMany<Purchase>()
    .From(dbo.Purchase)
    .OrderBy(db.fx.DateAdd(DateParts.Week, 1, dbo.Purchase.ShipDate))
    .Execute();

Group By Clause

Select product information grouped by product category type and the addition of 1 to ship date. (this example shows grouping, but the actual logic isn't any different than simply grouping by date created).

IEnumerable<dynamic> results = db.SelectMany(
        dbo.Product.ProductCategoryType,
        db.fx.DateAdd(DateParts.Week, 1, dbo.Product.DateCreated).As("NewDateCreated")
    )
    .From(dbo.Product)
    .GroupBy(
        dbo.Product.ProductCategoryType,
        db.fx.DateAdd(DateParts.Week, 1, dbo.Product.DateCreated)
    )
    .Execute();

Having Clause

Select purchase values grouped by payment method type that haven't shipped in the past week.

IEnumerable<dynamic> results = db.SelectMany(
        dbo.Purchase.PaymentMethodType,
        dbo.Purchase.ShipDate
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType,
        dbo.Purchase.ShipDate
    )
    .Having(
        db.fx.DateAdd(DateParts.Week, 1, dbo.Purchase.ShipDate) > DateTime.Now
    )
    .Execute();