CODEWITHSUNDEEP
sql-server
Reddy Sravan
Reddy Sravan

Reputation: 11

Calculating total minutes of an employee

select top 10 DATEDIFF(minute,[clock_in],[clock_out]) AS TOTAL_MINS

Getting below error

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Upvotes: 1

Views: 66

Answers (4)

Jagannath
Jagannath

Reputation: 41

As per my test I found below:

SELECT DATEDIFF(MINUTE,'0001-01-01 00:00:00.000','4083-12-31 00:00:00.000') AS TOTAL_MINS

TOTAL_MINS

2147124540

SELECT DATEDIFF(MINUTE,'0001-01-01 00:00:00.000','4084-05-19 00:00:00.000') AS TOTAL_MINS

TOTAL_MINS

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

So it clearly indicates that your datetime data value contains some invalid data.

Upvotes: 0

Eric
Eric

Reputation: 5743

Even SELECT DATEDIFF(MINUTE, '1753-01-01', '5836-01-01') do not cause the int overflow and much longer than human life, review your storage of employee clock_in and clock_out data.

Upvotes: 1

Sandeep Kumar
Sandeep Kumar

Reputation: 1202

You can use this query. May be its helps you.

select top 10 DATEDIFF(HOUR,[clock_in],[clock_out])*60 AS TOTAL_MINS

Upvotes: 0

Akshey Bhat
Akshey Bhat

Reputation: 8545

DATEDIFF's return value is an integer. If the return value is not in range of an integer then you will get an error. Try the below query to get minutes.

    select TOTAL_HRS*60 + DATEDIFF(mi,DATEADD(hh,CAST(TOTAL_HRS as int),Clock_in),clock_out) as Total_Mins
from

    (select top 10 DATEDIFF(hh,[clock_in],[clock_out]) AS TOTAL_HRS,clock_in,clock_out
    from tab) t

Upvotes: 0

Related Questions