我们如何在SQL服务器中使用varchar（5）数据类型时进行减法 [英] How can we substract while varchar(5) datatype use in SQL server

问题描述

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到EndTimeOut）是270分钟

MealBreakTimeSpent（MealTimeIn到MealTimeOut）是17分钟
TeaBreakTimeSpent（TeaTimeIn到TeaTimeOut）是11分钟

OtherBreakTimeSpent（OtherTimeIn到OtherTimeOut）为空



TotalTimeSpent = 242分钟

所以时间就像4:02，

我发送查询，请你纠正。



我尝试过的事情：

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:

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

数据类型varchar和varchar在减法运算符中不兼容。

The data types varchar and varchar are incompatible in the subtract operator.

推荐答案

简单：不要将值存储在VARCHAR字段中，而是使用更合适的数据类型。如果您正在尝试使用的是时间值（没有日期组件），则将其存储为包含自午夜以来的分钟数的整数。

然后您可以使用所需的所有算术在那，并使用除法和模数运算符将其转换回时间值。

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.

这篇关于我们如何在SQL服务器中使用varchar（5）数据类型时进行减法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！