Solved

# I need a help with this function

Posted on 2006-04-19
231 Views
Hello Guys.
I made a function that must return me the difference between two dates and return me it formated. But sometimes when I have only minutes in their difference it is not working, only returning me 0. Could you take a look where should I improve it? Thanks

CREATE FUNCTION DIFHOURS (@Dt1 Datetime, @Dt2 DateTime)
RETURNS char(10) AS
BEGIN
DECLARE @HOUR CHAR(10)

IF DATEDIFF(SECOND, @Dt1, @Dt2) / 3600 = 0
BEGIN
SET @HOUR = 0
END
ELSE
BEGIN
SET @HOUR =  CAST(DATEDIFF(SECOND, @Dt1, @Dt2) / 3600 AS VARCHAR(2)) + ':' +
RIGHT('0' + CAST(DATEDIFF(SECOND, @Dt1, @Dt2) %3600 / 60 AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEDIFF(SECOND, @Dt1, @Dt2) % 60 AS VARCHAR(2)), 2)
END

RETURN @HOUR
END
0
Question by:hidrau

LVL 142

Accepted Solution

CREATE FUNCTION DIFHOURS (@Dt1 Datetime, @Dt2 DateTime)
RETURNS char(10) AS
BEGIN
DECLARE @HOUR CHAR(10)

IF DATEDIFF(SECOND, @Dt1, @Dt2) / 3600 = 0
BEGIN
SET @HOUR = '0:00:00'
END
ELSE
BEGIN
SET @HOUR =  CAST(DATEDIFF(SECOND, @Dt1, @Dt2) / 3600 AS VARCHAR(2)) + ':' +
RIGHT('0' + CAST(DATEDIFF(SECOND, @Dt1, @Dt2) %3600 / 60 AS VARCHAR(2)), 2) + ':' +
RIGHT('0' + CAST(DATEDIFF(SECOND, @Dt1, @Dt2) % 60 AS VARCHAR(2)), 2)
END

RETURN @HOUR
END
0

LVL 1

Author Comment

angelIII I think you didn't understand, when the difference is only in minutes it is returning me none.

If I have this two date at this format: DD/MM/YYYY

1 date: 01/01/2006 15:35:00
2 date: 01/01/2006 15:36:00

the difference between is 2 minutes and the function is returning me 0
0

LVL 1

Author Comment

I got the error on it
It was at this line IF DATEDIFF(SECOND, @Dt1, @Dt2) / 3600 = 0
It must be this way IF DATEDIFF(SECOND, @Dt1, @Dt2) / 60 = 0

Even though Thanks
0

## Featured Post

### Suggested Solutions

SQL query syntax with WHERE and '%[ABC' clause 1 19
SQL Searching 3 35
sql sproc 2 22
Whats wrong with this query 21 26
I recently came across an interesting Question In EE (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/Q_27070749.html?cid=1135#a35876665) and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.