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
