SQL日期时间格式

Question

我需要一个表中的两列:

OverallTime | ElapsedTime
10          | 3:12:30

所以10是天数，3:12:30是3天12小时30分钟。然后我需要计算TimeLeft，它将是

OverallTime - ElapsedTime

什么是正确的数据类型，可以在sql数据库中表示这个?

SQL Server 2008 R2

Answer 1

For Sql-Server: OverallTime = days and ElapsedTime = 'days:hours:minutes'

declare @d datetime= getdate()
select OverallTime, 
       OverallTime + ':' + convert(varchar(5), @d, 108) ElapsedTime
from (
  select right(convert(varchar(8), @d, 112),2) OverallTime
  ) x

演示1

| OVERALLTIME | ELAPSEDTIME |
-----------------------------
|          07 |    07:17:32 |

如果您的意思是您的日期如所示，需要找到OverallTime - ElapsedTime，然后尝试(用列名替换@ot和@et);

declare @ot int = 10, @et varchar(10) = '3:12:30'
select datediff(minute,time, OverallTime-days) [OverallTime - ElapsedTime]
from (
select  @ot OverallTime, convert(int,left(@et,charindex(':',@et,1)-1)) days,
             right(@et, len(@et) - charindex(':',@et,1)) + ':00' time
  ) x

演示2

| OVERALLTIME - ELAPSEDTIME |
-----------------------------
|                      9330 |