Custom Function = HHHH:MM:SS - 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 2000 Forums

SQL Server Development (2000)

Custom Function = HHHH:MM:SS

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-04-04 : 17:39:54

Team:
I would like to design a custom string function within SQL. I don't know if there is any way to do this, but let's assume X is an integer in seconds (could be thousands of seconds, or just a few).
I wish to display it in HHHH:MM:SS format, where (HHHH could be thousands of hours, MM is minutes and SS is seconds.

The best code I can come up with is shown at the bottom of this entry. Now how do I convert it into a common function? I have many time values to convert, from many different columns.

I would like to use it as

SELECT X, HMS(X) as HMS FROM TableName

X      HMS
-----  --------
 2345  0:39:05
 4592  1:16:32
   65  0:01:05
    3  0:00:03
    0  0:00:00
98315  27:18:35

Here is my snippet to be converted into a function:


SELECT  
 CAST((X / 3600) AS VARCHAR) 
 + ':' 
 + 
 CASE 
   WHEN ((X % 3600) / 60) < 10 
   THEN '0' 
   ELSE '' 
 END 
  + CAST(((X % 3600) / 60) AS VARCHAR) 
  + ':'
  + 
  CASE 
    WHEN ((X % 3600) % 60) < 10 
    THEN '0' 
    ELSE '' 
  END 
  + CAST(((X % 3600) % 60) AS varchar)
  AS HMS

Thanks a lot!

Edited by - SMerrill on 04/04/2003 17:45:34

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 17:45:03

How about this:

CREATE FUNCTION udf_HHMMSS_Conversion
(@someinteger int)
RETURNS varchar(8)
AS

BEGIN

SELECT @someinteger =
CAST((X / 3600) AS VARCHAR)
+ ':'
+
CASE
WHEN (X / 60) < 10
THEN '0'
ELSE ''
END
+ CAST((X / 60) AS VARCHAR)
+ ':'
+
CASE
WHEN (X % 60) < 10
THEN '0'
ELSE ''
END
+ CAST(X % 60 AS varchar)
AS HMS

RETURN @someinteger

END

If it doesn't work, I think that you should be able to easily correct it since it's pretty straight forward. But let me know if you need more help.

Tara

Edited by - tduggan on 04/04/2003 17:59:21

SMerrill
Posting Yak Master

206 Posts

Posted - 2003-04-04 : 17:49:06

I neglected to mention that this is SQL 7 ...

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'FUNCTION'.
Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'X'.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'X'.
Server: Msg 137, Level 15, State 1, Line 27
Must declare the variable '@converted_datetime'.

--SMerrill
Seattle, WA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 17:58:40

Well user-defined functions aren't available in SQL Server 7. So you will need to convert it into a stored procedure instead with the value being outputted as a parameter.

Tara

Edited by - tduggan on 04/04/2003 18:04:41

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-04 : 18:00:05

I had a syntax problem in the code. I had to change @converted_datetime to @someinteger, hence the edit above by me.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 18:02:15

similar but a bit simpler.

convert(varchar(4), @x/3600)
+ ':'
+ right('0'+convert(varchar(2), (@x%3600)/60),2)
+ ':'
+ right('0'+convert(varchar(2), @x%60),2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 18:06:09

convert(varchar(4), @x/3600)
+ right(convert(varchar(8),dateadd(ss,@x,'19000101'),103),6)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bambola
Posting Yak Master

103 Posts

Posted - 2003-04-04 : 20:43:14

select convert(varchar(8), convert(datetime,(convert(binary(4),300 * @x))),108)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-05 : 07:31:00

That won't give more than 24 hours

oh and my last one should be 108 not 103

convert(varchar(4), @x/3600)
+ right(convert(varchar(8),dateadd(ss,@x,'19000101'),108),6)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 04/05/2003 07:47:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources