Click here to Skip to main content
16,019,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
StartTimeIn=11:48 am,
EndTimeOut =4:18 pm,

MealTimeIn=11:58 am,
MealTimeOut=12:15 pm,

TeaTimeIn= 1:06 pm,
TeaTimeOut=1:17 pm,

OtherTimeIn=null,
OtherTimeOut=null,

TimeSpent(StartTimeIn to EndTimeOut) is 270 minutes
MealBreakTimeSpent(MealTimeIn to MealTimeOut) is 17 minute
TeaBreakTimeSpent(TeaTimeIn to TeaTimeOut) is 11 minutes
OtherBreakTimeSpent(OtherTimeIn to OtherTimeOut) is null

TotalTimeSpent=242 minutes
so Time like 4:02,
I am sending my query can you please correct it.

What I have tried:

SQL
SELECT CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, StartTimeIn, ISNULL(EndTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) -
(
  CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, MealTimeIn, ISNULL(MealTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) +
  CONVERT(nvarchar(5), ISNULL( DATEADD(minute, DATEDIFF(minute, TeaTimeIn, ISNULL(TeaTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114) +
  CONVERT(nvarchar(5),ISNULL( DATEADD(minute, DATEDIFF(minute, OtherTimeIn, ISNULL(OtherTimeOut,SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))), 0),0), 114)
)  AS [TimeSpent] 
from DailyTimeRecord


The data types varchar and varchar are incompatible in the subtract operator.
Posted
Updated 11-Oct-16 23:15pm

1 solution

Simple: don't store your values in VARCHAR fields, and use more appropriate datatypes instead. If what you are trying to work with is a time value (without a date component) then store it as an integer which contain the number of minutes since midnight.
You can then to all the arithmetic you need on that, and convert it back to a "time" value very simply with the divide and modulus operators.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900