What actually you are trying to achieve?
What are the test cases?
Your Date_Diff function returning a varchar(20) why is that? when you are
talking about difference, it should be some numeric value.
And after putting a rough look at your code.
if (@year%4)=0
begin
set @days=28
end
else
begin
set @days=27
end
Can anybody tell me whats wrong with the above code?
On Tue, Jan 25, 2011 at 5:14 PM, The Master Mind <[email protected]>wrote:
> Yes Sir..
> Due to dat I have created my own date diff function which is flexible..
> But which gives 95% accuracy..5%it fails.. So Want the complete accuracy of
> 100% here is the function..
>
> CREATE function [Date_Diff] (
> @szFd_Date datetime,
> @szCurrent_Date datetime ,
> @handle varchar (20)
>
> )
> returns varchar(30)
> AS
> BEGIN
> declare @days int
> declare @mon int
>
>
> declare @years int
> declare @initialdate datetime
> declare @finalresult varchar(30)
> declare @checkmon int
> set @checkmon=0
> set @days=0
> set @mon=0
> set @years=0
> set @initialdate=@szFd_Date
> if(@szFd_Date > @szCurrent_Date)
> set @finalresult='invalid Dates'
> else
> begin
> set @years=datediff(year,@szFd_Date,@szCurrent_Date)
> set @checkmon =datediff(month,@szFd_Date,@szCurrent_Date)
> set @checkmon=datediff(month,@szFd_Date,@szCurrent_Date)
> if @years >0 and @checkmon = 12
> begin
> set @initialdate=dateadd(year,@years,
> @initialdate)
> end
> else
> begin
> if @checkmon > 12
> begin
> set
> @years=datediff(year,@szFd_Date,@szCurrent_Date)
>
> End
> else
> begin
> set @years=0
> End
> End
>
>
>
>
>
>
>
>
>
>
>
> -- Calculating months
> while @initialdate < @szCurrent_Date
> begin
> set @initialdate=dateadd(month,1,@initialdate)
> if @initialdate < @szCurrent_Date
> set @mon=@mon+1
> end
>
> --calculating days
>
> declare @fmon int , @fday int , @dy int , @lday int , @yr int , @fd
> int , @yrmn1 int
>
> set @fmon = month(@szfd_date)
> set @yr = year(@szfd_date)
> set @fday = day(@szfd_date)
> set @lday = day(@szCurrent_Date)
>
>
> set @dy = dbo.get_Month_Days(@fmon , @yr ) + 1
>
> set @fd = @dy- @fday
> if @fday > @lday
> begin
> set @days = @fd +@lday
> end
>
> else
> begin
> set @days = @lday - @fday
> end
>
> end
>
> if @handle= 'MONTH'
> begin
>
> --set @yrmn1= 0
>
> --if @years >0
>
> --begin
> --set @yrmn1 = 12 * @years
>
> --set @yrmn = 12
> --end
> --set @mon = @mon + @yrmn1
>
> set @finalresult=convert(varchar(2),@mon)
> end
>
> else if @handle= 'YEAR'
>
> begin
> set @finalresult=convert(varchar(2),@years)
> end
>
>
> else
> begin
> set @finalresult=convert(varchar(2),@years) +
> '#'+convert(varchar(3),@mon)+'#'+convert(varchar(2),@days)
> end
>
> return @finalresult
> --return @mon
>
> --return @Years
> End
>
>
>
> another function of getting days in the month....
>
>
> CREATE function get_Month_Days
> (
> @month_num int,
> @year int
> )returns int
> as
> begin
> declare @days int
> if @month_num=1
> begin
> set @days=30
> end
> if @month_num=2
> begin
> if (@year%4)=0
> begin
> set @days=28
> end
> else
> begin
> set @days=27
> end
> end
> if @month_num=3
> begin
> set @days=30
> end
> if @month_num=4
> begin
> set @days=29
> end
> if @month_num=5
> begin
> set @days=30
> end
> if @month_num=6
> begin
> set @days=29
> end
> if @month_num=7
> begin
> set @days=30
> end
> if @month_num=8
> begin
> set @days=30
> end
> if @month_num=9
> begin
> set @days=29
> end
> if @month_num=10
> begin
> set @days=30
> end
> if @month_num=11
> begin
> set @days=29
> end
> if @month_num=12
> begin
> set @days=30
> end
> return @days
> end
>
>
>
> On Tue, Jan 25, 2011 at 2:49 PM, Jamie Fraser <[email protected]>wrote:
>
>> You are a Senior Software ARCHITECT and that is the best question you can
>> come up with?!
>>
>>
>> On Mon, Jan 24, 2011 at 10:54 AM, The Master Mind <
>> [email protected]> wrote:
>>
>>>
>>> hii
>>>
>>> select datediff(month , '2010/12/14' , '2011/01/01') gives 1 month as
>>> date difference
>>> select datediff(day, '2010/12/14' , '2011/01/01') gives 18 days as
>>> date difference
>>> select datediff(year, '2010/12/14' , '2011/01/01') gives 1year as
>>> date difference
>>>
>>> kindly do help to find out the perfect datediff
>>> --
>>> Rakesh Hendre,
>>> Sr. Software Architect
>>> Brilliance Software Systems.
>>> http://www.brilliancesoftwares.com/
>>> Mob : 9960001862
>>>
>>
>>
>
>
> --
> Rakesh Hendre,
> Sr. Software Architect
> Brilliance Software Systems.
> http://www.brilliancesoftwares.com/
> Mob : 9960001862
>
