为什么这个带有DATEDIFF语句的INSERT不正常？

INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration) 
SELECT ixInterval, DATEDIFF(hour, dtStart, dtEnd) FROM fogbugz.dbo.TimeInterval
WHERE dtEnd is not NULL

IntervalID is an int, Duration is a float

IntervalID是一个int,Duration是一个浮点数

I am trying to put all the hourly durations into the Duration column... I assume that even if it is less than an hour it will use a fraction which is why I put a float?

我试图将所有小时持续时间放入持续时间列...我假设即使它不到一个小时它将使用一个分数,这就是为什么我放一个浮点数?

Help.

PS: It's running fine no errors, but after it's done the intervals table is still empty... I know the data is in the TimeInterval table though...

PS:它运行正常没有错误,但在完成后,interval表仍然是空的...我知道数据在TimeInterval表中虽然...

7 个解决方案

#1

I'm not sure why your data is not showing up, but DATEDIFF is not going to return a float. To get a float, you'll probably want to use a smaller unit of time and divide by the number of your units per minute. Example:

我不确定为什么你的数据没有出现,但是DATEDIFF不会返回浮点数。要获得浮动,您可能希望使用较小的时间单位除以每分钟的单位数。例:

DATEDIFF(second, dtStart, dtEnd) / (3600.0)

#2

DATEDIFF returns an Int of the number of date part boundaries crossed. I would expect you to get the floor of the number of hours duration, unless there is an issue with implicit conversion from Int to Float.

DATEDIFF返回跨越日期部分边界数的Int。除非存在从Int到Float的隐式转换的问题,否则我希望您能获得持续时间的最小值。

It is also helpful to post the error message received.

发布收到的错误消息也很有帮助。

#3

To insert actual fractions of an hour, use:

要插入一小时的实际分数,请使用:

INSERT INTO timecrunch.dbo.intervals (IntervalID, Duration) 
SELECT 
  ixInterval, 
  DATEDIFF(mi, dtStart, dtEnd) / 60.0
FROM 
  fogbugz.dbo.TimeInterval
WHERE 
  dtEnd is not NULL

DATEDIFF always returns you an INT value, never a fraction.

DATEDIFF总是返回一个INT值,绝不是一小部分。

#4

Try running the SELECT on its own with the INSERT bit, to check that your query is actually returning some data.

尝试使用INSERT位自行运行SELECT,以检查您的查询是否实际返回了一些数据。

Edit: As others have said, DATEDIFF returns an int, not a float, but that shouldn't prevent the INSERT from inserting some data.

编辑:正如其他人所说,DATEDIFF返回一个int,而不是一个浮点数,但这不应该阻止INSERT插入一些数据。

#5

First what results do you get just from running the select?

首先,您从运行选择中得到什么结果?

#6

In Sql Server DateDiff returns an int (See MSDN)

在Sql Server中,DateDiff返回一个int(参见MSDN)

#7

DATEDIFF will return an int not a float - is that the issue you are experiencing?

DATEDIFF将返回一个int而不是一个浮点数 - 这是您遇到的问题吗?

#1