SQL check year - 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

SQL check year

Author

Topic

daniel50096230
Yak Posting Veteran

99 Posts

Posted - 2010-12-29 : 09:30:21

Hi all, I have a table that contain services date.

For example, the date is 19/02/2009, so when I select the date out, it should calculate year of services. Assuming current month is December 2010, then the year of services from 19/02/2009 until 28/12/2010 should be 1.10 where 1 is one year and 10 is ten months.

How can I calculate this when I retrieving the data out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 09:53:11

something like


DATEDIFF(mm,datein,dateout)/12 +  (DATEDIFF(mm,datein,dateout)%12 *0.01)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-29 : 09:57:41

quote:
Originally posted by visakh16

something like

DATEADD(mm,datein,dateout)/12 + (DATEADD(mm,datein,dateout)%12 *0.01)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DATEADD should be DATEDIFF

Madhivanan

Failing to plan is Planning to fail

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-29 : 09:58:17

DECLARE @Today datetime
DECLARE @inDate datetime

SET @inDate = '20090219'
SET @today = getdate()

select datediff(month,@indate,@today) / 12+ (datediff(month,@indate,@today) % 12)/100.0

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 09:58:46

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

something like

DATEADD(mm,datein,dateout)/12 + (DATEADD(mm,datein,dateout)%12 *0.01)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DATEADD should be DATEDIFF

Madhivanan

Failing to plan is Planning to fail

oops nice catch
edited reply

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-29 : 09:59:24

Always hit refresh before posting!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources