DateDiff

2005
2008
2012
2014
2016
2017
2019
2022

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

DateDiff Date and Time Function

Use the DateDiff function to "generally" return the difference between two dates expressed in terms of {DATEPART}. See the Microsoft docs for a more technically correct explanation.

Syntax

db.fx.DateDiff({DATEPART}, {start_date_expression}, {end_date_expression})

Arguments

DATEPART: – The part of `start_date_expression` and `end_date_expression` to use in determining the difference. `DATEPART` is an enumeration (in the `DbExpression.MsSql.Expression` namespace).

start_date_expression: – The starting date.

end_date_expression: – The ending date.

Returns

int or int?

(based on nullability of `start_date_expression` and `end_date_expression`)

Examples

Select Statement

Select the days difference between a product's purchase date and it's ship date.

IEnumerable<int?> result = db.SelectMany(
        db.fx.DateDiff(DateParts.Day, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate)
    )
    .From(dbo.Purchase)
    .Execute();

Where Clause

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

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

Order By Clause

Select all purchases ordered by the difference in weeks between purchase date and ship date.

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

Group By Clause

Select product information grouped by payment method type and the difference in weeks between the purchase date and ship date.

IEnumerable<dynamic> results = db.SelectMany(
        dbo.Purchase.PaymentMethodType,
        db.fx.DateDiff(DateParts.Week, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate).As("WeeksBetween")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType,
        db.fx.DateDiff(DateParts.Week, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate)
    )
    .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,
        db.fx.DateDiff(DateParts.Day, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate).As("DaysBetween")
    )
    .From(dbo.Purchase)
    .GroupBy(
        dbo.Purchase.PaymentMethodType,
        db.fx.DateDiff(DateParts.Day, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate)
    )
    .Having(
        db.fx.DateDiff(DateParts.Day, dbo.Purchase.PurchaseDate, dbo.Purchase.ShipDate) < 7
    )
    .Execute();