August 9, 2010 at 1:42 am
i want the date differece to be showed as
05 days, 04 months, 04 year and my code is
declare @d datetime
declare @m datetime
declare @y datetime
declare @result varchar(50)
set @d= datediff(dd,'01/01/2010','01/03/2010')
set @m=datediff(mm,'01/01/2010','01/03/2010')
set @y=datediff(yy,'01/01/2010','01/03/2010')
set @result=convert(varchar,@d,102)+''+'days' + ','+ convert(varchar,@m)+''+ 'months'+','+ convert(varchar,@y) +''+'years'
print @result
i m getting result in :
1900.01.03days,Jan 1 1900 12:00AMmonths,Jan 1 19 format ..
I want in 05 days, 04 months, 04 year
August 9, 2010 at 2:45 am
DATEDIFF returns and integer, but you've declared the variables you return the results of the function to as DATETIME. These integer values are then getting implicitly converted to a datetime, which is where it's going wrong
August 9, 2010 at 6:31 am
here's an example that chops up the peices for you to concatenate:
/*
--results
Years Months Days Hours Minutes Seconds Milliseconds
-1 0 2 3 30 11 447
*/
select [Years ] = datediff(year,0,ET-ST)-1,
[Months] = datepart(month,ET-ST)-1,
[Days] = datepart(day,ET-ST)-1,
[Hours] = datepart(Hour,ET-ST),
[Minutes] = datepart(Minute,ET-ST),
[Seconds] = datepart(Second,ET-ST),
[Milliseconds] = datepart(millisecond,ET-ST)
from
(
select -- Test Data
ST = convert(datetime,'01/01/2010 00:35:33.997'),
ET = convert(datetime,'01/03/2010 04:05:45.443')
) a
Lowell
August 10, 2010 at 2:35 am
Check variables type
declare @d int /*datetime*/ --<<-- datetime wrong variable type
declare @m int /*datetime*/
declare @y int /*datetime*/
declare @result varchar(50)
set @d= datediff(dd,'01/01/2010','01/03/2010')
set @m=datediff(mm,'01/01/2010','01/03/2010')
set @y=datediff(yy,'01/01/2010','01/03/2010')
set @result=right('00'+convert(varchar,@d,102),2) + '' +'days'
+ ','+ right('00'+convert(varchar,@m),2) + '' + 'months'
+ ','+ right('00'+convert(varchar,@y),2) + '' + 'years'
print @result
August 11, 2010 at 4:04 am
When 50 months have passed, do you want to see:
50 months, 04 years
or
02 months, 04 years
?
declare @startdate datetime
declare @enddate datetime
set @enddate = '29-Aug-2015'
set @startdate = '11-Apr-2010'
-----------------------------------
declare @years int
declare @months int
declare @days int
declare @temp datetime
set @years = datediff ( yy, @startdate, @enddate )
set @temp = dateadd ( yy, @years, @startdate)
set @months = datediff ( mm, @temp, @enddate )
set @temp = dateadd ( mm, @months, @temp)
set @days = datediff ( dd, @temp, @enddate )
select right('00'+convert(varchar,@days ),2) + ' days, '
+ right('00'+convert(varchar,@months),2) + ' months, '
+ right('00'+convert(varchar,@years ),2) + ' years'
August 14, 2010 at 1:03 pm
Let's try that again:
create function dbo.
DATEDIFFYMD ( @STARTDATE datetime,
@ENDDATE datetime )
returns varchar(28)
as
begin
declare @years int
declare @months int
declare @days int
declare @tempyears datetime
declare @tempmonths datetime
set @years = datediff ( yy, @startdate, @enddate )
set @tempyears = dateadd ( yy, @years, @startdate)
if @tempyears > @enddate
begin
set @years = @years - 1
set @tempyears = dateadd ( yy, @years, @startdate)
end
set @months = datediff ( mm, @tempyears, @enddate )
set @tempmonths = dateadd ( mm, @months, @tempyears)
if @tempmonths > @enddate
begin
set @months = @months - 1
set @tempmonths = dateadd ( mm, @months, @tempyears)
end
set @days = datediff ( dd, @tempmonths, @enddate )
return right('00'+convert(varchar,@days ),2) + ' days, '
+ right('00'+convert(varchar,@months),2) + ' months, '
+ right('00'+convert(varchar,@years ),2) + ' years'
end
declare @startdate datetime
declare @enddate datetime
set @startdate = '29-Aug-2010'
set @enddate = '12-Feb-2015'
select dbo.DATEDIFFYMD ( @startdate,
@enddate ) as DIFF
result is:
14 days, 05 months, 04 years
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply