CODEWITHSUNDEEP
sql-server
user2603688
user2603688

Reputation: 171

how to add two different times in sql

I have a function with the following code in it:

DECLARE @requestedtime datetime,
    @Timeintervel int,
    @TotalTime datetime 

select @Timeintervel= datepart(MINUTE,l.Timeinterval)  
from Location_tbl l 
where l.LocName=@location

select @requestedtime= t.Paydate 
from Transaction_tbl t 
where t.TBarcode=@cardID

I am trying to do this:

  select @TotalTime=    DATEADD(MINUTE,@requestedtime,@Timeintervel)

...but it returns the following error:

Argument data type datetime is invalid for argument 2 of dateadd function.

Input parameters:

I want to get total time=2013-05-07 18:10:42.000

How can I get that? What function do I have to use?

Upvotes: 0

Views: 5528

Answers (2)

Jagadeesh G
Jagadeesh G

Reputation: 270

Try to use the following function to add minutes to DATE AND TIME:

DATEADD(MINUTE, @Timeintervel, @requestedtime)

If you have any doubts please go through the following links:

Upvotes: 0

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

Solution 1:

Note: I have seen you have questions tagged with SQL Server 2008.

SQL2008+ has TIME data type. In this case, I would use this solution:

DECLARE @RequestedTime TIME(0),
        @TimeIntervel TIME(0);

SELECT  @RequestedTime=CONVERT(TIME(0),'2013-07-20 00:25:00'),
        @TimeIntervel=CONVERT(TIME(0),'2013-07-25 10:10:10');

SELECT  @RequestedTime AS [@RequestedTime],
        @TimeIntervel AS [@TimeIntervel];

/*
@RequestedTime   @TimeIntervel
---------------- ----------------
00:25:00.0000000 10:10:10.0000000
*/

SELECT  DATEDIFF(SECOND,'00:00:00',@RequestedTime) AS ColDiff,
        DATEADD(SECOND,DATEDIFF(SECOND,'00:00:00',@RequestedTime),@TimeIntervel) AS ColFinalResult;

/*
ColDiff     ColFinalResult
----------- ----------------
1500        10:35:10.0000000
*/

Warning 1: You need a unique index on Location_tbl table (key: LocName) otherwise first query is non-deterministic (for diff. query executions you might get diff. results) though data is unchanged.

Warning 2: You need a unique index on Transaction_tbl table (key: TBarcode) otherwise second query is, also, non-deterministic (for diff. query executions you might get diff. results) though data is unchanged.

Solution 2: If you need to add only the minutes to a time value/variable.

DECLARE @RequestedTime_Minute INT,
        @TimeIntervel TIME(0);

SELECT  @RequestedTime_Minute=DATEPART(MINUTE,'2013-07-20T00:25:00'),
        @TimeIntervel=CONVERT(TIME(0),'2013-07-25 10:10:10');

SELECT  @RequestedTime_Minute AS [@RequestedTime_Minute],
        @TimeIntervel AS [@TimeIntervel];

/*
@RequestedTime_Minute @TimeIntervel
--------------------- ----------------
25                    10:10:10.0000000
*/

SELECT  DATEADD(MINUTE,@RequestedTime_Minute,@TimeIntervel) AS ColRequestedResult;

/*
ColRequestedResult
------------------
10:35:10.0000000
*/

Upvotes: 2

Related Questions