Date Calculations MS 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

SQL Server 2000 Forums

SQL Server Development (2000)

Date Calculations MS SQL

Author

Topic

13ina
Starting Member

5 Posts

Posted - 2009-10-27 : 20:22:47

Hello,

I am working on selecting HireDate from a table, and using the present date to work out the time an employee has been working with a company.

How can I get a more accurate result than with:

SELECT e.HireDate,datediff(year,e.HireDate,getdate()) AS Years
FROM HumanResources.Employee e

SELECT DATEDIFF(day, e.HireDate, getdate()) AS no_of_days
FROM HumanResources.Employee e

SELECT DATEDIFF(ww, e.HireDate, getdate()) AS no_of_weeks
FROM HumanResources.Employee e

Is it possible to work out years,months,weeks and days in the same query.

E.g. to end up with a result like:

name years months weeks days
-----------------------------
bob 22 3 1 2

Thank you

I am a newbie, and I dont want to create my own function, or procedure etc...:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-27 : 21:37:52

yes.


SELECT e.HireDate,
       datediff(year,e.HireDate,getdate()) AS Years, 
       datediff(month,e.HireDate,getdate()) AS Months, 
       DATEDIFF(day, e.HireDate, getdate()) AS no_of_days,
       DATEDIFF(ww, e.HireDate, getdate()) AS no_of_weeks
FROM   HumanResources.Employee e

KH
[spoiler]Time is always against us[/spoiler]

13ina
Starting Member

5 Posts

Posted - 2009-10-29 : 12:38:05

Actually, I mean breaking the date down consecutively into years, months, weeks and days.

For example, its like if I want to say,
I have been alive 25 years, 4 months, 2 weeks, and 1 day today.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-29 : 22:41:04

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources