I have two dates @Shutdownfrom and @shutdownto. I want to get the time difference of these two dates in hh:mm format but i also need to add a set of these time differences. For eg diff1 + diff2... etc in an rdlc report which is not possible if diff1 is a string in "hh:mm" format. Is there any way to get time difference in datetime format or is there any way to convert the varchar value got in "hh:mm" format to datetime I AM DOING IT IN SQL SERVER 2008
我有两个约会@Shutdownfrom和@shutdownto。我想以hh:mm格式得到这两个日期的时差,但我还需要添加一组这些时差。例如,对于rdlc报告中的diff1 + diff2 ...等,如果diff1是“hh:mm”格式的字符串,则这是不可能的。有没有办法在日期时间格式中获得时差,或者有没有办法将以“hh:mm”格式获得的varchar值转换为日期时间我在SQL SERVER 2008中的操作
1 个解决方案
#1
0
You should store time differences as an integer data type and leave the formatting to your client application.
您应该将时差存储为整数数据类型,并将格式保留给客户端应用程序。
For example, to calculate the difference between @Shutdownfrom and @Shutdownto you would typically use the DATEDIFF-function with an "M" value, to get the difference in integer minutes. You then store this value as an integer, and then you should have no problem adding difference values together (for example 67 minutes + 34 minutes = 101 minutes).
例如,要计算@Shutdownfrom和@Shutdownto之间的差异,通常会使用带有“M”值的DATEDIFF函数来获得整数分钟的差异。然后,您将此值存储为整数,然后将差值一起添加(例如67分钟+ 34分钟= 101分钟)应该没有问题。
If you must have your database format the integer amount of minutes as "hh:mm", use something like this in a view (assuming your integer column containing the differences is called Diff):
如果必须将数据库格式的整数分钟数量设置为“hh:mm”,请在视图中使用类似的内容(假设包含差异的整数列称为Diff):
SELECT CAST(Diff/60 AS VARCHAR) + ':' + RIGHT('0' + CAST(Diff % 60 AS VARCHAR)) AS [Diff_HHMM]
FROM MyTable
#1
0
You should store time differences as an integer data type and leave the formatting to your client application.
您应该将时差存储为整数数据类型,并将格式保留给客户端应用程序。
For example, to calculate the difference between @Shutdownfrom and @Shutdownto you would typically use the DATEDIFF-function with an "M" value, to get the difference in integer minutes. You then store this value as an integer, and then you should have no problem adding difference values together (for example 67 minutes + 34 minutes = 101 minutes).
例如,要计算@Shutdownfrom和@Shutdownto之间的差异,通常会使用带有“M”值的DATEDIFF函数来获得整数分钟的差异。然后,您将此值存储为整数,然后将差值一起添加(例如67分钟+ 34分钟= 101分钟)应该没有问题。
If you must have your database format the integer amount of minutes as "hh:mm", use something like this in a view (assuming your integer column containing the differences is called Diff):
如果必须将数据库格式的整数分钟数量设置为“hh:mm”,请在视图中使用类似的内容(假设包含差异的整数列称为Diff):
SELECT CAST(Diff/60 AS VARCHAR) + ':' + RIGHT('0' + CAST(Diff % 60 AS VARCHAR)) AS [Diff_HHMM]
FROM MyTable