Class DateDiff

Namespace

YndigoBlue.Velocity.Functions

Assembly

YndigoBlue.Velocity.dll

Represents a date/time difference function that returns the integer difference between two date, datetime, or timestamp values in the specified unit.

public class DateDiff : Function, ICheckItem, IDefaultItem, IFilterItem, IElement

Inheritance

object

Function

DateDiff

Implements

ICheckItem

IDefaultItem

IFilterItem

IElement

Remarks

DateDiff(DatePart, date1, date2) → date2 − date1 in the requested unit.

The datePart argument specifies the unit of measurement for the result (e.g. Year, Month, Day, Hour, Minute, Second). The result is date2 minus date1, so a positive value means date2 is later than date1.

The SQL generated is database-specific and is handled by each adaptor's BuildFunction override.

Examples

Calculate the number of days between each order date and the current date:

var schema = manager.LoadSchema("sales");
var orders = schema["orders"];

var query = new Query()
    .Select([ orders["id"], new Expression("days_since_order", new DateDiff(DatePart.Day, orders["order_date"], new Today())) ])
    .From(orders);

var results = manager.Retrieve(query);

Use the column + column constructor to calculate the number of days between two date columns:

var schema = manager.LoadSchema("projects");
var tasks = schema["tasks"];
Column startColumn = tasks["start_date"];
Column endColumn = tasks["end_date"];

var query = new Query()
    .Select([ tasks["id"], new Expression("duration_days", new DateDiff(DatePart.Day, startColumn, endColumn)) ])
    .From(tasks);

var results = manager.Retrieve(query);

Use the column + DateTime constructor to find how many years have elapsed since a fixed milestone:

var schema = manager.LoadSchema("hr");
var employees = schema["employees"];
Column hireDateColumn = employees["hire_date"];

var milestoneDate = new DateTime(2020, 1, 1);

var query = new Query()
    .Select([ employees["id"], new Expression("years_since_milestone", new DateDiff(DatePart.Year, milestoneDate, hireDateColumn)) ])
    .From(employees);

var results = manager.Retrieve(query);

Use the column + DateOnly constructor to measure months from a reference date to each record:

var schema = manager.LoadSchema("billing");
var invoices = schema["invoices"];
Column invoiceDateColumn = invoices["invoice_date"];

var cutoffDate = new DateOnly(2024, 1, 1);

var query = new Query()
    .Select([ invoices["id"], new Expression("months_since_cutoff", new DateDiff(DatePart.Month, cutoffDate, invoiceDateColumn)) ])
    .From(invoices);

var results = manager.Retrieve(query);

Constructors

DateDiff(DatePart, DateOnly, Column)

Initializes a new instance of DateDiff with a DateOnly start date and a column end date.

DateDiff(DatePart, DateTime, Column)

Initializes a new instance of DateDiff with a DateTime start date and a column end date.

DateDiff(DatePart, IElement, IElement)

Initializes a new instance of DateDiff.

DateDiff(DatePart, Column, DateOnly)

Initializes a new instance of DateDiff with a column start date and a DateOnly end date.

DateDiff(DatePart, Column, DateTime)

Initializes a new instance of DateDiff with a column start date and a DateTime end date.

DateDiff(DatePart, Column, Column)

Initializes a new instance of DateDiff with both dates as columns.

Properties

DatePart

Gets the date/time component unit used for measuring the difference.