DateAdd in SQL Vs Excel
-
Adding 3 months in Excel and SQL Server return different dates.
When we add 3 months to 11/30/2006 in SQL server using dateadd(month, 3, '11/30/2006'), it returns 2/28/2007.
If we use DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) in Excel, it returns 3/2/2007.
Is there any equivalent built-in function in SQL Server to get the result returned by Excel. I understand that I would write a query to get the same date but just want to know if there is any built-in function that would cater to this requirement.
-
I'm kind of confused - SQL in my mind is returning the right result, and Excel is not? If I am on the last day of a month, and add three months, I should be on the last day of a month as well, shouldn't I?
Looks to me like Excel is doing a (adding x months is the same as adding x time 31 days) which in my mind would be two entirely different calculations?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
It's because Excel does things a bit differently. The third parameter of your DATE function specifies which day to use when turning it into a serial date. So, it's returning the 30th day of February, which is March 1st or 2nd, depending on whether or not it's a leap year.
This should do the trick, as it simply adds three months, without specifying the day to use:
DATE(YEAR(A1),MONTH(A1)+3,0)
That's the Excel equivalent of Dateadd(month, 3, '11/30/2006')
-
David,
I tried the formula that you have given in Excel and it always returns the last day of the 3rd month which is not the equivalent of dateadd(month, 3, date). I am looking for a built-in SQL function, if any, for the result returned by Excel for the formula DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))
Matt,
Our client's requirement is to match the Excel result!. I am not sure why MS has implemented 2 of their products to return different results for the same scenario. The formula DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) is the one used in Excel to add 3 months.
-
You can create a VBA macro for use on the cell. VBA supports DATEADD exactly the same as SQL.
-
Ah, you want to duplicate Excel's behavior, not fix it? What Excel does with the Date function is take the year as passed, the month as passed, and the day as passed, and build a date from them. Using your example, what it does behind the scenes is ( from my understanding, although I didn't create Excel
):Starting Values:
Year = 2006
Month = 11
Day = 30
Ending Values:
Year = 2006 -Yes, this is not a typo
Month = 14
Day = 30
Since Month is over 12 but less than 24, it increments Year by 1, and subtracts the 12 from Month, making Year now 2007, and Month now 2 (February). Then, it sees that Days are 30, and since 2007 is not a leap year, it realizes that this goes beyond the end of February, but not beyond the end of March, so it changes Month to 3 (March), subtracts the number of days in February from Days (resulting in 2), and makes that the Day.
You can easily see this in action with a function like =Date(2006,50,100), which will return 5/11/2010. Remember, the purpose of Date isn't necessarily to accomplish date arithmetic, it just does so instead of erroring out when the parameters are outside of the standard range.
If you're looking for something that will handle all of those things in SQL, I wish you luck on your programming challenge. It's doable, but will take a bit of effort to get things just right to cover all situations. I'd recommend you read the notes with the parameters of the Date function in Excel if you're going to do so. Also, you'll need to handle undocumented things, such as that 0 for the date that I showed above.
If you only want to emulate it for month addition, let us know, as that should actually be a bit easier to accomplish.
-
Thanks much for the input David. This is only for month addition and I guess there is no built-in T-SQL function that I could use for this.
-
Not built in (well there is, but not the way Excel does), but if it's just month, it's fairly easy to do. It still seems odd that the client would want this, but sometimes we just dance with the one who brung us without asking a lot of questions.
Take the day value from the source date and compare it to the day value from the result of Dateadd(month, x, sourcedate). If the first one is equal to the second, you're done. If the first one is greater, then subtract the latter from the former and do a Dateadd(day, difference, destinationdate).
Source date: 11/30/2006
Let's add one month first:
DateAdd(month, 1, '11/30/2006') returns 12/30/2006. The day values are equal, so we're done.
Now we'll add three months:
DateAdd(month, 3, '11/30/2006') returns 02/28/2007. The latter day value is lower, so we need to do the next steps. Subtracting 28 from 30 leaves 2, so do a DateAdd(day, 2, '02/28/2007') and you have the result you are looking for.
I hope that helps.
-
Thanks much David. This indeed is a very good one.
-
alter
function Excel_monthadd(@datefield datetime, @inc int) returns datetime
as
begin
declare
@tempdate datetime
declare
@curr_daypart int
declare
@max_endmonth int
set
@tempdate = dateadd(m,@inc,@datefield) --the SQL calculation date
set
@curr_daypart=Day(@datefield) --the day of the month we're starting from
set
@max_endmonth=day(dateadd(d,-1*(day(@tempdate)),dateadd(m,1,@tempdate))) -- what we have to look at
--do the SQL dateadd then adjust if the ending month is less than the current day
Return
dateadd(d, case when @curr_daypart>@max_endmonth then @curr_daypart-@max_endmonth else 0 end,@tempdate)
end
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I followed David's logic to get the following
declare @a datetime
set @a = '10/31/2006'
select
case when day (@a) day(dateadd(month, 6, @a)) then
dateadd(day, day(@a) - day(dateadd(month, 6, @a)) , dateadd(month, 6, @a) )
else dateadd(month, 6, @a) end.
This is actually part of a select query in a Stored proc and has to find the result for each record and works fine now.
Thanks everybody for the input.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply