Datediff function - 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

General SQL Server Forums

New to SQL Server Programming

Datediff function

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-04-25 : 05:23:06

hi
select datediff(m,'3/5/2003',getdate as experience

output is
Experience
----------
49

select (datediff(m,'3/5/2003',getdate()))/12 as experience

output is
Experience
----------
4

but 49/12 is 4.08333=4.1

how i can get this
Actually my task is to output as years.months

Thanks in advance

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-25 : 05:28:25

datediff returns integer, integer divide by integer will give you an integer.
using 12.0 will convert the result to decimal

select (datediff(m,'3/5/2003',getdate()))/12.0 as experience

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-25 : 05:33:38

select cast(datediff(m,'3/5/2003',getdate()) as float)/12 as experience

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-04-25 : 06:38:02

quote:
Originally posted by pbguy

select cast(datediff(m,'3/5/2003',getdate()) as float)/12 as experience

ya it's ok
but the o/p of u r query is 4.083333 '
but i want as 4.1

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 06:42:13

Round the output to 1 decimal places.

Select round((datediff(m,'3/5/2003',getdate()))/12.0,1) as experience

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 06:43:00

or another solution is:

select convert(numeric(25,1), (datediff(m,'3/5/2003',getdate()))/12.0) as experience

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-25 : 12:24:44

Things to watch out for when using DATEDIFF
http://www.sqlteam.com/item.asp?ItemID=26922

Peter Larsson
Helsingborg, Sweden

naveen901
Starting Member

2 Posts

Posted - 2007-04-26 : 04:08:33

quote:
Originally posted by jogin malathi

hi
select datediff(m,'3/5/2003',getdate as experience

output is
Experience
----------
49

select (datediff(m,'3/5/2003',getdate()))/12 as experience

output is
Experience
----------
4

but 49/12 is 4.08333=4.1

how i can get this
Actually my task is to output as years.months

Thanks in advance

Malathi Rao

hi

try this

Select (datediff(m,'3/5/2003',getdate()))/12.0 as experience

thanks

naveen.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources