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.
| Author |
Topic |
|
mdelgado
Posting Yak Master
141 Posts |
 Posted - 2002-06-25 : 16:03:43
|
| Hello all...I'm haveing trouble calculating a column named 'Length of Employment'.I have the [Name] and [HireDate] available and I need to calculate the length of employment for all employees. I'm using the syntax below:DATEDIFF(yy, HIREDATE, GETDATE()) AS [Length of Emp.]the problem is, that all employees who have been here between 1 and 23 months, it's calculating as 1.I need it to round out in decimal format. I need it to show for example someone who started on 1/1/02 as '.48'Any help would be much appreciated... |
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-06-25 : 16:13:37
|
| Try something like this:SELECT CAST(DATEDIFF(dd, HIREDATE, CURRENT_TIMESTAMP)/365. AS numeric(5, 2)) AS [Length of Emp.] FROM YourTable--HTH,Vyashttp://vyaskn.tripod.com |
 |
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2002-06-25 : 16:21:58
|
| Excellent! thank you.Any idea how I can get the number to be right justified? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-25 : 16:51:10
|
| Instead of CAST, use STR(), it will pad spaces on the left:SELECT STR(DATEDIFF(dd, HIREDATE, getdate())/365.0, 5, 2) AS [Length of Emp.] FROM YourTable |
|
|
|
mdelgado
Posting Yak Master
141 Posts |
Posted - 2002-06-25 : 16:53:14
|
| You da man!!thanks again Rob. |
|
|
|
|
|
|
|
|
Simple Date Calculation...