Sumarize minutes - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Sumarize minutes

Author

Topic

Gearwheel
Starting Member

4 Posts

Posted - 2011-08-30 : 08:34:37

Hi there. On SQL Server 2008 R2 I try to sumarize some fields (time(0)) with the following statement in a scalar-function:
DECLARE @Result TIME(0)

SELECT @Result = DATEADD(MINUTE,REBZeit,0) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,RSOZeit,@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,RAMZeit,@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,RTVZeit,@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)

RETURN @Result

/*Meldung 8116, Ebene 16, Status 1, Prozedur ufs_Reklamation_Zeit, Zeile 15
Der Argumentdatentyp time ist für das 2-Argument der dateadd-Funktion ungültig.

The problem is the second argument in the dateadd-Function. But it´s how it´s written in BooksOnline!?

Does somebody know how this statemant could work (and easier)?

Best regards from Austria
Martin

Best regards from Austria
Martin Sachers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-30 : 08:51:12

the 2nd parameter of dateadd() should be an integer. What is the data type for your REBZeit, RSOZeit etc ?

KH
[spoiler]Time is always against us[/spoiler]

Gearwheel
Starting Member

4 Posts

Posted - 2011-08-30 : 08:54:11

The Datatype for all the fields is TIME(0)!
Martin

Best regards from Austria
Martin Sachers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 09:28:58

you cant add a time to date like this. perhaps what you're after is this?


SELECT @Result = DATEADD(MINUTE,DATEDIFF(MINUTE,0,REBZeit),0) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,DATEDIFF(MINUTE,0,RSOZeit),@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,DATEDIFF(MINUTE,0,RAMZeit),@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)
SELECT @Result = DATEADD(MINUTE,DATEDIFF(MINUTE,0,RTVZeit),@Result) FROM dbo.tblR1 WHERE (ReklamationsID = @ReklamationsID)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gearwheel
Starting Member

4 Posts

Posted - 2011-08-30 : 09:54:12

Sorry, but that doesn´t work. In the first select-Resultset you will get the same error which i got!

Best regards from Austria
Martin Sachers

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-30 : 09:55:26

Or perhaps this ?


SELECT 	@Result = DATEADD(MINUTE, 
			DATEDIFF(MINUTE, 0, REBZeit) 
			+ DATEDIFF(MINUTE, 0, RSOZeit) 
			+ DATEDIFF(MINUTE, 0, RAMZeit) 
			+ DATEDIFF(MINUTE, 0, RTVZeit), 0)
FROM 	dbo.tblR1 
WHERE 	(ReklamationsID = @ReklamationsID)

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:19:59

quote:
Originally posted by Gearwheel

Sorry, but that doesn´t work. In the first select-Resultset you will get the same error which i got!

Best regards from Austria
Martin Sachers

nope thats not possible
see this example


declare @T time(0),@T2 time(0)

SELECT @T='12:30:00'

SELECT @t2=DATEADD(minute,DATEDIFF(minute,0,@T),0)
SELECT @T2

[green]
output
----------------------------

12:30:00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:19:59

quote:
Originally posted by Gearwheel

Sorry, but that doesn´t work. In the first select-Resultset you will get the same error which i got!

Best regards from Austria
Martin Sachers

nope thats not possible
see this example


declare @T time(0),@T2 time(0)

SELECT @T='12:30:00'

SELECT @t2=DATEADD(minute,DATEDIFF(minute,0,@T),0)
SELECT @T2


output
----------------------------

12:30:00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gearwheel
Starting Member

4 Posts

Posted - 2011-08-30 : 11:23:44

As i won´t get it going i turned the fields to int.
But many thanks for helping me!

Best regards from Austria
Martin Sachers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 11:25:30

why? it worked for me. Unless you're doing something different it should work for you too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources