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 |
|
jimmyo88
Starting Member
2 Posts |
 Posted - 2012-09-03 : 11:02:19
|
| could anyone give me some assistance please. I am trying to create a new column which gives the difference between the in_time and out time in minutes. However, I am getting strange results, The DIFFTIME values are shown as "7.12222 and "6.55555555". I want them to be shown as 7.1 and 6.5 etc. I have tried using the round function but cannot get it work. Can anyone help please.[CODE]SELECT worker_id , MAX(CASE WHEN entering_site = 1 THEN access_date END) AS in_time , MAX(CASE WHEN entering_site = 0 THEN access_date END) AS out_time , DATEDIFF (n, MAX(CASE WHEN entering_site = 1 THEN access_date END), MAX(CASE WHEN entering_site = 0 THEN access_date END)) / 60.0 as hours_worked FROM access_transaction WHERE worker_id = 1 GROUP BY worker_id , DATEPART(yyyy,access_date) , DATEPART(mm,access_date) , DATEPART(dd,access_date)[/CODE]Many thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-03 : 15:32:05
|
| whats the datatype of access_date?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-09-04 : 07:08:43
|
| [code]CAST(DATEDIFF (n, MAX(CASE WHEN entering_site = 1 THEN access_date END), MAX(CASE WHEN entering_site = 0 THEN access_date END)) / 60.0 AS DECIMAL(12,1)) as hours_worked[/code]MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Rounding a Date as a new column