DATE DAX Function (Date and Time)
Returns the specified date in datetime format.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Year |
A four digit number representing the year. |
|
| Month |
A number from 1 to 12 representing the month of the year. |
|
| Day |
A number from 1 to 31 representing the day of the month. |
Return values
Returns the specified date.
Remarks
A DateTime value can be specified as a literal by using the prefix dt before a string with one of the following formats:
- dt”YYYY-MM-DD”
- dt”YYYY-MM-DDThh:mm:ss”
- dt”YYYY-MM-DD hh:mm:ss”
For example, DATE ( 2023, 5, 19 ) corresponds to dt”2023-05-19″.
Notes about the arguments:
- If Year is between 0 and 49 (inclusive), DATE adds 2000 to the value. For example, DATE ( 8, 1, 2 ) returns January 2, 2008.
- If Year is between 50 and 99 (inclusive), DATE adds 1900 to the value. For example, DATE ( 98, 1, 2 ) returns January 2, 1998.
- If Year is between -1800 and -1 (inclusive), DATE adds 1900 to the value. For example, DATE ( -50, 1, 2 ) returns January 2, 1850.
- If Year is between 100 and 9999 (inclusive), DATE uses the value as the year. For example, DATE ( 2008, 1, 2 ) returns January 2, 2008.
- If Year is 10000 or greater, DATE returns an error. If Year is less than -1800, the result is a date before the year 100. Other functions, for example YEAR and FORMAT, return an error with that date.
- If Month is greater than 12, DATE adds the extra months to the year. For example, DATE ( 2008, 14, 2 ) returns February 2, 2009.
- If Month is 0 or negative, DATE goes back from January of Year by the magnitude of Month plus one month. For example, DATE ( 2008, 0, 2 ) returns December 2, 2007, and DATE ( 2008, -3, 2 ) returns September 2, 2007.
- If Day is greater than the number of days in the month, DATE adds the extra days to the month. For example, DATE ( 2008, 1, 35 ) returns February 4, 2008.
- If Day is negative, DATE goes back from the first day of the month by the magnitude of Day plus one day. For example, DATE ( 2008, 1, -15 ) returns December 16, 2007. If Day is 0, DATE returns an error.
- DATE rounds decimal arguments to the nearest integer. For example, DATE ( 2008, 1, 2.5 ) returns January 3, 2008.
- A BLANK argument is the same as 0. For example, DATE ( 2008, BLANK (), 2 ) returns December 2, 2007, and DATE ( 2008, 1, BLANK () ) returns an error.
This function is not supported in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
» 1 related function
Examples
-- DATE and TIME are useful to create DateTime columns
-- A DateTime is a number. Therefore, it is possible to
-- sum the date and the time part.
EVALUATE
{
DATE ( 2020, 10, 15 ),
TIME ( 22, 45, 30 ),
DATE ( 2020, 10, 15 ) + TIME ( 22, 45, 30 )
}
| Value |
|---|
| 2020-10-15 00:00:00 |
| 1899-12-30 22:45:30 |
| 2020-10-15 22:45:30 |
-- When the arguments overflow their range, DATE and TIME
-- behave in different ways:
--
-- DATE adds the excess values to the date, moving time forward
-- TIME adds the excess values, but it does never exceed the day
EVALUATE
{
DATE ( 2020, 10, 32 ),
DATE ( 2020, 20, 1 ),
TIME ( 10, 90, 0 ),
TIME ( 50, 0, 0 )
}
| Value |
|---|
| 2020-11-01 00:00:00 |
| 2021-08-01 00:00:00 |
| 1899-12-30 11:30:00 |
| 1899-12-30 02:00:00 |
-- You can use DATE to create a new date, one year later
-- Using built-in functions like EDATE is better, if they are available
EVALUATE
ADDCOLUMNS(
TOPN ( 5, VALUES ( 'Date'[Date] ), 'Date'[Date], ASC ),
"One year later",
DATE (
YEAR ( 'Date'[Date] ) + 1,
MONTH ( 'Date'[Date] ),
DAY ( 'Date'[Date] )
),
"One year later (Using EDATE)",
EDATE ( 'Date'[Date], 12 )
)
| Date | One year later | One year later (Using EDATE) |
|---|---|---|
| 2005-01-05 | 2006-01-05 | 2006-01-05 |
| 2005-01-04 | 2006-01-04 | 2006-01-04 |
| 2005-01-02 | 2006-01-02 | 2006-01-02 |
| 2005-01-01 | 2006-01-01 | 2006-01-01 |
| 2005-01-03 | 2006-01-03 | 2006-01-03 |
Related articles
Learn more about DATE in the following articles:
-
Comparing cumulative values for events in different periods
This article describes how to compare time series that occur in different periods by standardizing the timelines to days since a specific event. » Read more
Related functions
Other related functions are:
Last update: Nov 9, 2026 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Philippe Le Page
Microsoft documentation: https://learn.microsoft.com/en-us/dax/date-function-dax