DATE

The DATE function returns a date in datetime format.

Syntax

DATE(
    year,
    month,
    day
)

Parameters
  • year: Number representing the year.
  • month: Number representing the month.
  • day: Number representing the day.
Returned value

The DATE function returns a value in datetime format that represents a date.

Additional Information

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:

DATE function: Example of use

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:

DATE function: Example of use

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:

DATE function: Example of use

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:

DATE function: Example of use

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:

DATE function: Example of use

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:

DATE function: Example of use

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.

DATE function: Example of use

And so on.

If this argument is not an integer, it is rounded to the nearest integer:

DATE function: Example of use

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:

DATE function: Example of use

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:

DATE function: Example of use

While for February 29, 2016 we obtain the following result:

DATE function: Example of use

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:

DATE function: Example of use

If this argument is not an integer, it is rounded to the nearest integer:

DATE function: Example of use

Related functions
Category
Date and time
Submitted by admin on Mon, 12/03/2018 - 23:36