datetime diff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime diff

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-08-09 : 03:42:03

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

scoo

PavanKK
Starting Member

32 Posts

Posted - 2010-08-09 : 03:49:40

Just datatype change will serve your need

declare @d varchar(20)--datetime
declare @m varchar(20)--datetime
declare @y varchar(20)--datetime

KK

asgast
Posting Yak Master

149 Posts

Posted - 2010-08-09 : 03:51:35

declare @d int
declare @m int
declare @y int
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=CAST(@d as varchar(2))+''+'days' + ','+ CAST(@m as varchar(2))+''+ 'months'+','+ CAST(@y as varchar(4)) +''+'years'

print @result

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-09 : 04:48:29

See http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources