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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy