Forum Discussion
0 Value for Fields in DATE function
If that was the only part of the documentation, then I agree, having 0 as an error would be fair. But the section I included in my initial post seems to clearly specify the expected behaviour of 0: giving the last day of the previous month. Also, the documentation gives very similar rules for month:
"A number representing the month or a calculation according to the following rules:
If month is a number from 1 to 12, then it represents a month of the year. 1 represents January, 2 represents February, and so on until 12 that represents December.
If you enter an integer larger than 12, the following computation occurs: the date is calculated by adding the value of month to the year. For example, if you have DATE( 2008, 18, 1), the function returns a datetime value equivalent to June 1st of 2009, because 18 months are added to the beginning of 2008 yielding a value of June 2009. See examples below.
If you enter a negative integer, the following computation occurs: the date is calculated subtracting the value of month from year."
But having experimented, <month> = 0 isn't an error but a valid way to express the last month of the previous year.
Also, if -1 gave the last day of the previous month, I think it would be fair to have 0 give an error. But it doesn't; it gives the date 2 days before. So for example, if I wanted to use the format DATE(2016, 2, x), I can generate essentially every date imaginable EXCEPT for January 31, 2016. That doesn't seem reasonable. Anyway, I'll get over it. Thanks!
I had exactly the same issue. It's illogical.
The simple fix would be to provide a DATEADD function for scalar dates. (DATEADD only works with columns).
s.