Dear Sir, Dear Mr, Mrs and All Friends use Microsoft's SQL Sever !
SQL Sever is very Friends,very fast and ...But....
OK, I think but have a problem, only 1 problem I can Found until now, a proplem of people-logic – I thinh so.
I write to you to tell about a problem stay in your Function what I think it is a large Problem. I am very sory if you have knowed it.
OK, now I wil explain about this problem anh the way to fix it or at least, it is the way that you must be explain to your customer how to use it for their work.
It is a problem of DateAdd('m',DateTime,Number of Month Add) funcction !
Your function work very good with all normal day in the Year but in the the day of the last of the month it return the wrong value, it is very funny. Example, when i work with the day is 30 of the month:
DateAdd('m',1,'30/11/2008')='30/12/2008'
DateAdd('m', 1,'30/12/2008')='30/01/2009'
DateAdd('m', 1,'30/01/2008')='28/02/2009'
DateAdd('m', 1,'28/02/2009')='28/03/2009'
DateAdd('m', 1,'28/03/2009')='28/04/2009'
DateAdd('m', 1,'28/04/2009')='28/05/2009'
...
If I add 1 month then After the function, the index of day return is not the same, why ? Because the February not have enough 30 days, and after February the value of date return have the same day: 28. In case of the day near the last month day, we try:
DateAdd('m',1,'31/01/2009')='28/02/2009'
DateAdd('m', 1,'30/01/2009')='28/02/2009'
DateAdd('m', 1,'29/01/2009')='28/02/2009'
DateAdd('m', 1,'28/01/2009')='28/02/2009'
DateAdd('m', 1,'27/01/2009')='27/02/2009'
DateAdd('m', 1,'26/01/2009')='26/02/2009'
...
As You see, the 4 day after using the Function return the same day and have no Explain why it is ?
I knowed why and ou know why, OK ! Because wre are Engeneering computer (but my customer is not). We knowed that datetime is not the same as integer, not the same as the any kind of the number in the computer. But it is very friendsly with people on 3 or 4 thousand years, even when the first computer not absent in the World.
If the normal number can using like: for(i=1;i<n;i++), then the operator ++ is knowed that "the Next" value of index. If we can using the Next value of the datetime type, the is no problem, so it is very important.
This problem is the large problem when using this function to calculating automatic Scheduled on the computer. It will not return the same day and my customer will ask why ? Why they can not work with us in the same day of the month? Why the day is not only if the first time they work with us in the day from 28 to 31 of the first Month ?
We can not explain, some time we must using input day by hand to Scheduled time to working with our customer !
Some time we mus us the fix day for our work, example the day is 06, may be:
the first term: 06 and /02/2008 = 06-Feb-2008
the next term: 06 and /03/2008
the next term: 06 and /04/2008
the next term: 06 and /05/2008
the next term: 06 and /06/2008
...
But when we use the day is 29 to 31 there is a large problem. Example for the day is 29:
the first term: 29 and /11/2009
the next term: 29 and /12/2009
the next term: 29 and /01/2009
the next term: 29 and /02/2010 = 29-Feb-2010: Wrong value, not have this day on DateTime type
the next term: 29 and /03/2010
...
I think you can fix the problem by using Zezo – Datetime: It is the "zezo term" where you can using DateAdd function to get the all term you want !
The Zezo – Datetime is the day of the previours year, of december and the index of day is the same day you need. Now we can calculate the day of all term by this way:
The first day is 30 of novenber 2009, the next day must be 30 of the another Month (of cause, this Month the function return have at least 30 day Else it is the last day of the Month):
The Zezo – Datetime: 30-Dec-2008:
The first term: 30-11-2009 = DateAdd('m',11, 30-Dec-2008): 11= Index of month
The first term: 30-12-2009 = DateAdd('m',12, 30-Dec-2008)
The first term: 30-01-2010 = DateAdd('m',13, 30-Dec-2008)
The first term: 28-02-2010 = DateAdd('m',14, 30-Dec-2008)
The first term: 30-03-2010 = DateAdd('m',15, 30-Dec-2008)
The first term: 30-04-2010 = DateAdd('m',16, 30-Dec-2008)
The first term: 30-05-2010 = DateAdd('m',17, 30-Dec-2008)
The first term: 30-06-2010 = DateAdd('m',18, 30-Dec-2008)
The first term: 30-07-2010 = DateAdd('m',19, 30-Dec-2008)
The first term: 30-08-2010 = DateAdd('m',20, 30-Dec-2008)
The first term: 30-09-2010 = DateAdd('m',21, 30-Dec-2008)
You can see:
11 = 11+0 = Index of first Month + 0
12 = 11+1 = Index of first Month + 1
13 = 11+2 = Index of first Month + 2
14 = 11+3 = Index of first Month + 3 and ....
But 0,1,2,3... is the values of the normal Index of all: For, Do while and While Looping when you are coding in your Program.
So this way is you can calculate the next term with the Zezo – Datetime and the Index of the Month of the first term
In your database, if you save Zezo – Datetime (DateStart) and the Index of the first term month (InitTerm) in the table SysDateID:
SysDateID
(I can not view table data here, Sorry !)
ID DateStart InitTerm Data ...
001 30-Dec-08 8 ...
You can using 1 Update command to calculating all term like:
Update DataTable
Set TermDate = DateAdd('m',
TermID + (Select InitTerm From SysDateID Where SysDateID.ID=DataTable.ID) -1,
(Select DateStart From SysDateID Where SysDateID.ID=DataTable.ID))
And the values of all term here:
DataTable
ID TermID TermDate Data ... ...
001 1 30-Aug-09 ...
001 2 30-Sep-09
001 3 30-Oct-09
001 4 30-Nov-09
001 5 30-Dec-09
001 6 30-Jan-09
001 7 28-Feb-10
001 8 30-Mar-10
...
...
I wonder the Add_Month of Oracle have the same problem when calculating with DateTime type, even their function always return the last day of month if the first value is the last day of the month. Oh, very funny, do you think so ?
So, I think this problem can explain why Excel (the most people know it) not have both Add_month and DateAdd function, they are to diffi*** to use.
Of cause, you can add 1 parametter to DateAdd fo mean: The day would be return like:
DateAdd(FixDate,'m',DateTime,Number of Month to add)
Parameter FixDate mean: you try to return the day this day, in case of can not re turn Fixday it will be return the index of day betteen FixDate and the date of "DateTime" parameter Input. Of cause, the Fixdate parametter only use when the date of "DateTime" parametter >= 28
I think, if you can't change your function, you can't add more function like DateAdd with Fixdate parametter add more, you need add my example with Zezo-DateTime to your Help files, Help online and all Microsoft SQL Book and send to your Customer as the explain to using DateAdd('m',,) Function. And I think they will never say DateAdd('m',,) is the very difficult to use.
Thanks for reading my letter !
Hanoi, Viet Nam 06-Mar-2009
Le Cong Dai – 302 A13 Kim Giang – ThanhXuan District Hanoi City
Office: 46A TangBatHo Hanoi City (84)-(043)-9713312 or 9712341
E-Mail: Lecongdair577@yahoo.com
[Tai Yee - MSFT] Hi, the best way to submit product feedback like this is to use the Microsoft Connect Web site, available at http://connect.microsoft.com/sqlserver.