The DATE function returns a date in datetime format.
DATE(
year,
month,
day
)
- year: Number representing the year.
- month: Number representing the month.
- day: Number representing the day.
The DATE function returns a value in datetime format that represents a date.
year
This argument can include one to four digits: If it has one or two digits, it adds up to 1900. Thus, "Fecha = DATE(10,12,31)" returns the date of December 31, 1910:
If it has three or four digits, it is not changed.
If year is a real number, it is rounded to the nearest integer. In the following example, the value 1016.7 is entered as the first argument, being rounded to 1917:
If year is greater than 9999, DATE returns an error message. If it is a value less than -1800, DATE also returns an error message:
However, if it is a negative value between -1800 and 0 (both values included), it is added to 1900. Thus, if the value -100 is entered as an argument, DATE interprets it as 1900-100=1800:
month
If this argument is an integer between 1 and 12 it is interpreted as the corresponding month (1 represents January and 12 represents December). If it is a number greater than 12, the integer part of dividing the value of month by 12 is added to the year argument and the value of month is considered as the month modulo 12. That is, 12 is subtracted from month as many times as possible (until there is a number between 1 and 12 left) and each value of 12 subtracted increases the value of year by one. For example, if 2000 is entered as the year, 12 as the month, and 1 as the day, DATE returns December 1, 2000 as the date. But if 13 is entered as the month, the date returned is the one corresponding to one month later of the following year: January 1, 2001:
To understand how the DATE function handles a zero or negative month value, the simplest way is to start with an example like DATE(2000,1,1). This function returns the date of January 1, 2000. If we increase month by one, it is obvious that we would get the date corresponding to one month later (February 1, 2000). Well, in a similar way, if we decrease month by one (to leave it at zero), we also obtain the displaced date but, in this case, one month back, returning the date of December 1, 1999:
In the same way, if month takes the value -1, the date would be adjusted to return one more month back, obtaining the date of November 1, 1999.
And so on.
If this argument is not an integer, it is rounded to the nearest integer:
day
If this argument is a number between 1 and the number of days in the specified month, it represents the day of the month. However, if the number of days indicated by this argument exceeds the number of days in the month, the excess days are added to the resulting date. For example, if we enter 1 for the month (January, 31-day month) and 33 for the day, the returned date is February 2:
DAX automatically calculates the number of days in each month taking into account, for example, leap years. In this way, for February 29, 2015 we obtain the following result:
While for February 29, 2016 we obtain the following result:
The behavior of the function when the day argument takes a null or negative value is similar to that seen for the month with a small difference: day cannot take the value 0 (DATE returns an error message), although it can take negative values. In any case, to understand the result that the DATE(2000, 1, -1) function would return, it is convenient to imagine that DATE(2000,1,0) would return the date of December 31, 1999 as a result, so DATE(2000,1, -1) returns one day less: December 30, 1999:
If this argument is not an integer, it is rounded to the nearest integer: