sum of nvarchar datatype - 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

sum of nvarchar datatype

Author

Topic

sql23
Starting Member

1 Post

Posted - 2009-03-24 : 12:31:54

Hi,

I have a table with few records. I have two columns with EmpName and Number of Hours worked.

Number of Hrs worked column is in Nvarchar Datatype. I would like to get the Sum(NUM oF HRS WORKED).to generate a report. IS there any way i could do that.Thanks for yoru help.

EX:

EMPLOYEENAME NUM OF HRS WORKED

1 8:30
2 9:00
3 8:00

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-24 : 13:18:33

You can use the fact that Sql Server will implicitly convert these values to the datetime values. (assmuming that "8:30" means 8 hrs and 30 minutes.)
So this:

select sum(datediff(minute, 0, [Num of hrs worked])) from <yourTable>

will return 1530 as an integer which is the total number of minutes. How do you want the value displayed? like "25:30" ? If so you will have to do something like this:


select 
       convert(varchar(3), sum(datediff(minute, 0, [Num of hrs worked])) / 60) 
       + ':' 
       + convert(varchar(2), sum(datediff(minute, 0, [Num of hrs worked])) % 60)
from   <yourTable>

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 13:02:14

and if you want the total for each employee remember to group by employeename and then take sum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources