Why isn't this INSERT with DATEDIFF statement working right?

Question

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

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...

Answer 1

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

Answer 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.

It is also helpful to post the error message received.

Answer 3

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(second, dtStart, dtEnd) / (3600.0)

Answer 4

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

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

Answer 5

In Sql Server DateDiff returns an int (See MSDN)

Answer 6

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

Answer 7

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.