Difference of two datetime columns caused overflow

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Difference of two datetime columns caused overflow

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-02-25 : 08:58:29

Hi,

I've have this database which has about 3,8 mio rows at the moment.

When I want to execute this query i'm getting the above error.

My frontend asp app. sql query :
SELECT DateAdd(s, Sum(DateDiff(s, 0, samtalevarighed)), 0) AS TotalTime FROM usage WHERE Year(samtalestart) ='"&Aar&"' AND Month(samtalestart) = '"&ValgMaaned&"'

I then tried to create a temp table, too see if that would help instead. but unfortunately i'm getting the same error.

CREATE TABLE #TempUsage
(
ID int IDENTITY,
Kontraktnummer nvarchar(50),
Mobilnummer nvarchar(50),
Opkaldstype nvarchar(255),
Opkaldsmodtager nvarchar(255),
Samtalestart datetime,
Samtalevarighed datetime,
Samtalepris nvarchar(50),
)

INSERT INTO #TempUsage (Kontraktnummer,Mobilnummer,Opkaldstype,Opkaldsmodtager,Samtalestart,Samtalevarighed,Samtalepris)
SELECT KontraktNummer,MobilNummer,Opkaldstype,Opkaldsmodtager,Samtalestart,Samtalevarighed,Samtalepris FROM Usage WHERE Year(samtaleStart) = '2008' AND Month(SamtaleStart) = '1' ORDER BY Samtalestart DESC

SELECT DateAdd(s, Sum(DateDiff(s, 0, samtalevarighed)), 0) AS TotalTime FROM #TempUsage WHERE Year(samtalestart) ='2008' AND Month(samtalestart) = '1'

Anyone of you who please can help out, so the sql query will execute properly ?

Looking forward to hear from you.

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 09:13:19

~~Sum(DateDiff(s, 0, samtalevarighed)),~~

Sum(samtalevarighed),

samtalevarighed already is in seconds?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 09:18:19

This way you also can take benefit of an existing index over SamtaleStart column

DECLARE	@Aar SMALLINT,
	@ValgMaaned TINYINT

SELECT	@Aar = 2009,
	@ValgMaaned = 2

-----------------------------------------------------
DECLARE	@FromDate DATETIME,
	@ToDate DATETIME

SELECT	@FromDate = DATEADD(MONTH, 12 * @Aar - 22801 + @ValgMaaned, 0),
	@ToDate = DATEADD(MONTH, 1, @FromDate)

SELECT	SUM(Samtalevarighed) AS TotalTime
FROM	Usage
WHERE	Samtalestart >= @FromDate
	AND Samtalestart < @ToDate

E 12°55'05.63"
N 56°04'39.26"

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-02-26 : 04:51:55

Thanks Peso for your answer.

No the samtalevarighed is in HH:MM:SS format.

But as soon as I change my datediff to what you suggested ( SELECT DateAdd(s, Sum(samtalevarighed), 0) AS TotalTime FROM #TempUsage WHERE Year(samtalestart) ='2008' AND Month(samtalestart) = '1') i'm getting this error : Operand data type datetime is invalid for sum operator.

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 09:36:33

The reason for overflow is when DateDiff(s, 0, samtalevarighed) exceeds 2147483647 seconds.
Does the samtalevarighed contain date also? I mean, other dates than 1900-01-01 ?

SELECT DATEADD(s, Sum(DateDiff(s, 0, DATEADD(DAY, DATEDIFF(DAY, samtalevarighed, 0), samtalevarighed))), 0) AS TotalTime
FROM usage WHERE Year(samtalestart) ='"&Aar&"' AND Month(samtalestart) = '"&ValgMaaned&"'

E 12°55'05.63"
N 56°04'39.26"

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-02-26 : 10:10:20

no only the date you posted 1900-01-01

Best regards
Taz

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-02-26 : 10:18:24

Thanks alot Peso it works again :)

Can you please explain what your modified query does exactly ? :)

Best regards
Taz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:53:50

The DATEADD(DAY, DATEDIFF(DAY, samtalevarighed, 0), samtalevarighed) removes date information, no matter if it is 1900-01-01 or 1899-12-30 or any other date.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:56:10

If you want to use index in the query (if index exists over samtalestart column,

use something like this

SELECT DATEADD(s, Sum(DateDiff(s, 0, DATEADD(DAY, DATEDIFF(DAY, samtalevarighed, 0), samtalevarighed))), 0) AS TotalTime
FROM usage WHERE samtalestart >= '" & Date(Aar, ValgMaaned, 1) & "' AND samtalestart < '" & date(aar, ValgMaaned + 1, 1) & "'

or better

SELECT DATEADD(s, Sum(DateDiff(s, 0, DATEADD(DAY, DATEDIFF(DAY, samtalevarighed, 0), samtalevarighed))), 0) AS TotalTime
FROM usage WHERE samtalestart >= '" & format(Date(Aar, ValgMaaned, 1), "yyyymmdd") & "' AND samtalestart < '" & format(date(aar, ValgMaaned + 1, 1), "yyyymmdd") & "'

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 10:57:36

quote:
Originally posted by TazMania

no only the date you posted 1900-01-01

You can easily check that with

SELECT * FROM usage where datediff(day, 0, samtalevarighed) <> 0

Run that to see which samtalevarighed is not on 1900-01-01.

E 12°55'05.63"
N 56°04'39.26"

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-02-28 : 07:38:51

Thanks again :)

Your help is sooo much appreciated. thanks.

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources