i have problems with sql - 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

i have problems with sql

Author

Topic

hopeful
Starting Member

1 Post

Posted - 2010-01-12 : 14:57:36

hello every body.am so happy to be with you here.besides i really want to benefit from you explanation because i have some problems with sql.one of these problems is: how to calculate the years that a worker has worked in a company by using the option of sysdate.
thanks

i just want to learn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-12 : 15:07:14

Isn't sysdate an Oracle function? This is a Microsoft SQL Server site.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-12 : 16:37:40

Use GETDATE() for sql server

Jack Vamvas
--------------------
http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:30:44

let his date of joining be in a field called DOJ in emp table. then use like below


SELECT CASE WHEN MONTH(DOJ) > MONTH(GETDATE()) OR (MONTH(DOJ) = MONTH(GETDATE()) AND DAY(DOJ) > DAY(GETDATE())) THEN DATEDIFF(yy,DOJ,GETDATE())-1 ELSE DATEDIFF(yy,DOJ,GETDATE()) END AS YearsWorked FROM emp

Sachin.Nand

2937 Posts

Posted - 2010-01-13 : 01:55:15

declare @dateofjoining datetime
select @dateofjoining='12-jan-2000'
select DATEDIFF(yy,0,getdate()) - datediff(yy,0,@dateofjoining) as NoofYears

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 01:57:52

quote:
Originally posted by Idera

declare @dateofjoining datetime
select @dateofjoining='12-jan-2000'
select DATEDIFF(yy,0,getdate()) - datediff(yy,0,@dateofjoining) as NoofYears

PBUH

what happens if he joined in Dec 2000? as per your calculation it gives 10. is it correct?

Sachin.Nand

2937 Posts

Posted - 2010-01-13 : 02:12:04

it wont be correct but since OP asked "no of years" & "not no of years & months".So it will be correct.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 02:33:42

quote:
Originally posted by Idera

it wont be correct but since OP asked "no of years" & "not no of years & months".So it will be correct.

PBUH

i dont think so. I think the reported result should be 9 years not 10 as only after dec 2010 it crosses 10 year mark

also see below

http://www.sqlteam.com/article/datediff-function-demystified

Sachin.Nand

2937 Posts

Posted - 2010-01-13 : 02:36:10

Wow!!!
Thnx for that great link.You are right :)

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 02:40:16

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources