September 28, 2007 at 12:18 pm
Hi,
I'm relatively new to programming with sql, but I haven't had much difficulty picking it up
I'm writing a function that requires the difference in months between two dates
(more specifically in my example case, 11/30/2005 and today (9/28/2007))
I wrote a simple function to perform this task so I could re use it a few times without typing out the datediff function each time.
looks like:
create function numMonthsElapsed(@startDate AS datetime)
returns int
begin
return datediff(month, @startDate, getDate())
END;
but when I put in my sample start date i'm getting 1292 returned, definantly not right..
As far as I know datediff should return a datatype of int,
but I check the system functions folder and it says its returning datetime
Help?
Thanks
M. Hovde
September 28, 2007 at 12:36 pm
Verify that the date in @startDate is actually the date you passed. If you do this: select datediff(month, 0, getdate()) you get a return value of 1292, the number of months between the "zero" date and today.
😎
September 28, 2007 at 12:40 pm
It returns 22 for me.
Check the function and make sure it really is what you sent me.
What is GETDATE() returning? Is your server date wrong?
September 28, 2007 at 12:41 pm
I figured it out, I'm just tired and apparently blind.
nothing was wrong with my code, I was just entering the datetime data without single quotes.
😎
September 28, 2007 at 8:15 pm
Just for the ones who never had this problem... what he did is enter something like this :
01/01/2007 instead of '01/01/2007'
The net result is often a date damn near 1900-01-01
1 divided by 1 divided by 2007 is pretty much always between 0 and 1... which is then computed as the number of days since day 0 (1900-01-01).
October 1, 2007 at 8:28 am
http://www.sqlteam.com/article/datediff-function-demystified
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply