#datetime - DateTime Function

#datetime is a Power Query M function that creates a datetime value from numbers representing the year, month, day, hour, minute, and (fractional) second.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

#datetime(
   year as number,
   month as number,
   day as number,
   hour as number,
   minute as number,
   second as number,
) as datetime

Description

The #datetime function creates a datetime value from specified numbers representing the year, month, day, hour, minute, and (fractional) second.

Required input for each component:

Year: Must be between 1 and 9999 (inclusive).
Month: Must be between 1 and 12 (inclusive).
Day: Must be between 1 and 31 (inclusive).
Hour: Must be between 0 and 23 (inclusive).
Minute: Must be between 0 and 59 (inclusive).
Second: Must be between 0 and less than 60.

If any of these conditions are not met, the function will return an error message stating: “The DateTime operation failed because the resulting value falls outside the range of allowed values.”

Examples

Let’s see how the #datetime function works.

Creating a Simple Datetime Value

To create a datetime value for July 1, 2024, at 3:45:30 PM, you can use the following code:

#datetime( 2024, 7, 1, 15, 45, 30 )

This code specifies the year 2024, the month of July, the 1st day, 15 hours (3 PM), 45 minutes, and 30 seconds.

Working with Fractional Seconds

You can also include fractional seconds in your datetime value. For example, to create the datetime July 1, 2024, at 3:45:30.250 PM (250 milliseconds), use this code:

#datetime( 2024, 7, 1, 15, 45, 30.250 )

Handling Invalid Values

The #datetime function raises an error if any component is outside its valid range. For example, if you try to create a datetime value with an invalid day:

/* Output: "Expression.Error: The DateTime operation failed because the 
   resulting value falls outside the range of allowed values" */ 

#datetime( 2024, 7, 32, 15, 45, 30 )

This code will result in an error because July 32 is not a valid date.

Extracting Datetime Components

You can extract individual components (year, month, day, hour, minute, and second), as well as the date or time component from a datetime value using specific functions:

Date.Year( #datetime( 2024, 7, 1, 15, 45, 30 ) )     // Output 2024

Date.Month( #datetime( 2024, 7, 1, 15, 45, 30 ) )    // Output 7

Date.Day( #datetime( 2024, 7, 1, 15, 45, 30 ) )      // Output 1

DateTime.Time( #datetime( 2024, 7, 1, 15, 45, 30 ) ) // Output #time( 15, 45, 30 )

DateTime.Date( #datetime( 2024, 7, 1, 15, 45, 30 ) ) // Output: #date( 2024, 7, 1 )

Time.Hour( #datetime( 2024, 7, 1, 15, 45, 30 ) )     // Output: 15

Time.Minute( #datetime( 2024, 7, 1, 15, 45, 30 ) )   // Output: 45

Time.Second( #datetime( 2024, 7, 1, 15, 45, 30 ) )   // Output: 30

Other functions related to #datetime are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/sharpdatetime