'datetime' or 'INT' is not a recognized CURSOR option.

Msg 155, Level 15, State 2, Line 1
'datetime' is not a recognized CURSOR option.

We get an error of this kind when we miss prefixing @ symbol to the variable name during declaration.

Below examples reproduce this error:

if we execute the below statement 

DECLARE intVar INT;

We will end up with below error

Msg 155, Level 15, State 2, Line 1

‘INT’ is not a recognized CURSOR option.

if we execute the below statement 

DECLARE Var2 VARCHAR(50)

We will end up with below error

Msg 155, Level 15, State 2, Line 1

‘VARCHAR’ is not a recognized CURSOR option.

Fix this by adding prefix the variable by the @ symbol in the variable declaration statement.



https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers?view=sql-server-2017

Round DateTime to nearest Hour or minute or second or day.

declare @dt datetime

set @dt = '09-22-2019 23:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0) --  truncate the minute part to get to start of that hour.

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0) ---- rounde to next minute
select dateadd(hour, datediff(hour, 0, dateadd(mi, 60, @dt)), 0) -- rounded to next hour.
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)--- truncate the minute part to get to start of that hour.


---coming from below link

https://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

will return

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

and you'll get:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

---

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days int
set @days = datediff(day, 0, @dt)

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

or more succinctly:

select dateadd(day, datediff(day, 0, @dt), 0)

Using a different datepart (e.g. hour, mi) will work accordingly.

How to calculate No of minutes per Hour in time range.

Use below logic

Make a dimension called Dim_Hours and do a cross join with the table that contains begin and end timestamps.
 dim_hour is a static table with data like this

Hour_Number	Hour_Label	Hour_Order
0	12:00 AM	19
1	1:00 AM	20
2	2:00 AM	21
3	3:00 AM	22
4	4:00 AM	23
5	5:00 AM	24
6	6:00 AM	1
7	7:00 AM	2
8	8:00 AM	3
9	9:00 AM	4
10	10:00 AM	5
11	11:00 AM	6
12	12:00 PM	7
13	1:00 PM	8
14	2:00 PM	9
15	3:00 PM	10
16	4:00 PM	11
17	5:00 PM	12
18	6:00 PM	13
19	7:00 PM	14
20	8:00 PM	15
21	9:00 PM	16
22	10:00 PM	17
23	11:00 PM	18

--when the difference is +ve
-- 7 - 0 7 +ve
-- 7 - 1 6 +ve
-- 7 - 2 5 +ve
-- 7 - 3 4 +ve
-- 7 - 4 3 +ve
-- 7 - 5 2 +ve
-- 7 - 6 1 +ve

--when the difference is Zer0
-- 7 - 7 0 Zero
--when the difference is -ve

-- 7 - 8 -1 -ve
-- 7 - 9 -2 -ve
-- 7 - 10 -3 -ve
-- 7 - 11 -4 -ve
-- 7 - 12 -5 -ve
-- 7 - 13 -6 -ve
-- 7 - 14 -7 -ve
-- 7 - 15 -8 -ve
-- 7 - 16 -9 -ve
-- 7 - 17 -10 -ve
-- 7 - 18 -11 -ve
-- 7 - 19 -12 -ve
-- 7 - 20 -13 -ve
-- 7 - 21 -14 -ve
-- 7 - 22 -15 -ve
-- 7 - 23 -16 -ve

use the above explanation to write your SQL

WITH TEMP AS
  (SELECT [ID] ,
          [On_DateTime] ,
          [Off_DateTime] ,
          [On_Duration_Minutes] ,
          dh.Hour_Number
   FROM [dbo].[tableWithData] f
   CROSS JOIN dim_Hour dh
   WHERE 1=1 )--when -ve and hour part matches start of hour

SELECT t.*,
       [On_DateTime],
       dateadd(HOUR, datediff(HOUR, 0, dateadd(mi, 60, [On_DateTime])), 0),
       DATEDIFF(SECOND, [On_DateTime], dateadd(HOUR, datediff(HOUR, 0, dateadd(mi, 60, [On_DateTime])), 0))/60.0 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) <= -1
  AND t.Hour_Number =datepart(HH, [On_DateTime])
UNION --when -ve and hour part matches end of hour

SELECT t.*,
       dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0),
       [Off_DateTime],
       DATEDIFF(SECOND, dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0), [Off_DateTime])/60.0 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) <= -1
  AND t.Hour_Number =datepart(HH, [Off_DateTime])
UNION --when -ve and hour part between start and end of hour excludint boundries

SELECT t.*,
       dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0),
       [Off_DateTime],
       60.00 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) <= -1
  AND t.Hour_Number >datepart(HH, [On_DateTime])
  AND t.Hour_Number< datepart(HH, [Off_DateTime])
UNION --when 0 and hour part matches with start/end

SELECT t.*,
       dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0),
       [Off_DateTime],
       CASE
           WHEN on_Duration_minutes<0 THEN 0
           ELSE on_Duration_minutes
       END on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) = 0
  AND t.Hour_Number=datepart(HH, [On_DateTime])
UNION --when +ve and hour part matches start of hour

SELECT t.*,
       [On_DateTime],
       dateadd(HOUR, datediff(HOUR, 0, dateadd(mi, 60, [On_DateTime])), 0),
       DATEDIFF(SECOND, [On_DateTime], dateadd(HOUR, datediff(HOUR, 0, dateadd(mi, 60, [On_DateTime])), 0))/60.0 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) >= 1
  AND t.Hour_Number =datepart(HH, [On_DateTime])
UNION --when +ve and hour part matches end of hour

SELECT t.*,
       dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0),
       [Off_DateTime],
       DATEDIFF(SECOND, dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0), [Off_DateTime])/60.0 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) >= 1
  AND t.Hour_Number =datepart(HH, [Off_DateTime])
UNION --when +ve and hour part between start of hour and end of hour

SELECT t.*,
       dateadd(HOUR, datediff(HOUR, 0, [Off_DateTime]), 0),
       [Off_DateTime],
       60.00 on_Duration_minutes_Derived
FROM TEMP t
WHERE 1=1
  AND datepart(HH, [On_DateTime]) - datepart(HH, [Off_DateTime]) >= 1
  AND t.Hour_Number!=datepart(HH, [On_DateTime])
  AND t.Hour_Number!=datepart(HH, [Off_DateTime])
  AND (t.Hour_Number <datepart(HH, [Off_DateTime])
       OR t.Hour_Number > datepart(HH, [On_DateTime]))
ORDER BY 2,
         3,
         4