Date, Time, and Duration Types

M has five distinct temporal types. Mixing them up is one of the most common sources of type errors and unexpected behavior in Power Query.

The Five Temporal Types

| Type | What it represents | Example literal | |---|---|---| | date | Calendar date only — no time | #date(2024, 3, 15) | | time | Time of day only — no date | #time(14, 30, 0) | | datetime | Date + time, no timezone | #datetime(2024, 3, 15, 14, 30, 0) | | datetimezone | Date + time + UTC offset | #datetimezone(2024, 3, 15, 14, 30, 0, -5, 0) | | duration | A span of time — days, hours, minutes, seconds | #duration(1, 2, 30, 0) |

Conversions Between Types

From datetime to its parts:

let
    dt = #datetime(2024, 3, 15, 14, 30, 0),
    d  = DateTime.Date(dt),       // #date(2024, 3, 15)
    t  = DateTime.Time(dt)        // #time(14, 30, 0)
in
    {d, t}

Combining date and time into a datetime:

let
    d = #date(2024, 3, 15),
    t = #time(14, 30, 0),
    dt = d + t    // date + time = datetime
in
    dt
// Result: #datetime(2024, 3, 15, 14, 30, 0)

Adding a timezone offset:

DateTimeZone.From(#datetime(2024, 3, 15, 14, 30, 0))
// Uses the local machine's timezone offset

DateTimeZone.SwitchZone(#datetimezone(2024, 3, 15, 14, 30, 0, -5, 0), 0, 0)
// Converts to UTC (#datetimezone(2024, 3, 15, 19, 30, 0, 0, 0))

Stripping the timezone (for storage or joining):

DateTimeZone.RemoveZone(#datetimezone(2024, 3, 15, 14, 30, 0, -5, 0))
// Result: #datetime(2024, 3, 15, 14, 30, 0) — offset discarded

Duration Arithmetic

Durations are the result of subtracting two dates or datetimes of the same type:

#date(2024, 3, 15) - #date(2024, 1, 1)
// Result: #duration(74, 0, 0, 0)  — 74 days

#datetime(2024, 3, 15, 14, 30, 0) - #datetime(2024, 3, 15, 12, 0, 0)
// Result: #duration(0, 2, 30, 0)  — 2 hours, 30 minutes

Extract the components of a duration:

let
    d = #duration(3, 14, 30, 45),
    days    = Duration.Days(d),       // 3
    hours   = Duration.Hours(d),      // 14
    minutes = Duration.Minutes(d),    // 30
    seconds = Duration.Seconds(d),    // 45
    total   = Duration.TotalHours(d)  // 86.5125
in
    {days, hours, minutes, seconds, total}

You can add a duration to a date or datetime to shift it forward:

#date(2024, 1, 1) + #duration(30, 0, 0, 0)
// Result: #date(2024, 1, 31)

Common Pitfalls

Comparing date to datetime fails silently. M is strict about type compatibility:

// This will error:
#date(2024, 3, 15) = #datetime(2024, 3, 15, 0, 0, 0)

// Do this instead:
DateTime.Date(#datetime(2024, 3, 15, 0, 0, 0)) = #date(2024, 3, 15)
// Result: true

DateTime.LocalNow() vs DateTimeZone.LocalNow(). DateTime.LocalNow() returns the machine's local time as a datetime (no offset). DateTimeZone.LocalNow() includes the UTC offset. In scheduled refreshes (Power BI Service, Dataflows), "local" is the server's timezone — often UTC — not your local machine.

Column type vs value type. Setting a column's type to type date via Table.TransformColumnTypes does not strip the time component from underlying values that arrived as datetimes — it converts them. If conversion fails (malformed strings, mixed types), cells become errors. Always inspect source data before applying a strict temporal type.

Duration is not a number. You cannot multiply a duration by a scalar directly:

// This errors:
#duration(1, 0, 0, 0) * 7

// Use Duration.TotalDays to get a number first, or add repeatedly:
Duration.From(Duration.TotalDays(#duration(1, 0, 0, 0)) * 7)
// Result: #duration(7, 0, 0, 0)

Quick Reference: Key Conversion Functions

| Function | From → To | |---|---| | DateTime.Date(dt) | datetime → date | | DateTime.Time(dt) | datetime → time | | DateTime.From(d) | date → datetime (time = 00:00:00) | | DateTimeZone.From(dt) | datetime → datetimezone (local offset) | | DateTimeZone.RemoveZone(dtz) | datetimezone → datetime | | DateTimeZone.ToLocal(dtz) | datetimezone → local datetimezone | | DateTimeZone.ToUtc(dtz) | datetimezone → UTC datetimezone | | Duration.TotalDays(dur) | duration → number (days) | | Duration.TotalHours(dur) | duration → number (hours) | | Duration.TotalMinutes(dur) | duration → number (minutes) | | Duration.TotalSeconds(dur) | duration → number (seconds) |