date difference not working fine in sqlserver

Posted by Klbaiju under Sql Server on 2/22/2014 | Points: 10 | Views : 602 | Status : [Member] | Replies : 2
Hi,
this is working code
SELECT EmpId ,CAST(InTime AS DATE) as workingday ,EmpName,
--error in below line
RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108) < '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, InTime, OutTime)/60.0 END))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM(case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, InTime, OutTime) END)%60), 2) AS [totalwork_hours)],
convert(varchar(5), max(OutTime)-MIN(InTime),108) as Emp_Intime,dstatus=
(CASE
WHEN CONVERT(TIME,MIN(InTime),108)>'08:35' and CONVERT(TIME,MIN(InTime),108) <'11:00'
THEN 'L'
WHEN CONVERT(TIME,MIN(InTime),108)>'11:00'
THEN 'halfday'
else 'right' end )
FROM baiju.dbo.hbaemp
GROUP BY EmpId , EmpName, CAST(InTime AS DATE)

error is in Totalwork_hours
if intime is greater than outtime is working fine
every intime is greater than outtime except first intime
i changed like this
RIGHT('0' + CONVERT(varchar, FLOOR(SUM(case when CONVERT(TIME,(InTime),108) < '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI, OutTime,InTime)/60.0 END))) , 2) + ':' + RIGHT('0' + CONVERT(varchar, SUM(case when CONVERT(TIME,(InTime),108)< '12:45' and CONVERT(TIME,(OutTime),108)> '01:30' then DATEDIFF( MI,OutTime,InTime) END)%60), 2) AS [totalwork_hour]
but not working fine




Responses

Posted by: Sravan661 on: 2/22/2014 [Member] Bronze | Points: 25

Up
0
Down
hi
refer this
http://technet.microsoft.com/en-us/library/ms189794.aspx

sravan

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Snaveen on: 2/24/2014 [Member] Starter | Points: 25

Up
0
Down
Hi,

In SQL we can get the difference between 2 dates by using Datediff function.
Below is the sample for datediff funciton, use the above foramt and modify the content based on your need.

[code]
select datediff(DAY,getdate(),getdate()+5)
[/code]


Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response