Date/Time Math

# Thread: Date/Time Math

1. Senior Member
Join Date
Dec 1969
Posts
480

## Date/Time Math

How do I convert a number into hh:mm:ss?<BR>I am doing DTS packages in SQL Server 2000 and the source database stores the time of events as (for example) 54540 which is the total seconds passed that day. I need to convert this to hh:mm:ss and then tack it onto the end of a date value (which thankfully is passed as normal). I know it begins with divide the number by 3600 and then multiply the decimal part???<BR>This will prob be needed in the future so an answer in the form of a function (UDF) would be nice.<BR><BR>pass the int to the function return the answer in time format.<BR>Many thanks - I know it&#039;s a bit off topic.<BR><BR>Jay

2. Senior Member
Join Date
Dec 1969
Posts
480

## reworded - no bloat version!

Hi People,<BR>Just realised that half of what I wrote was bloatware.<BR>Simple version. I pass a (whole) number to a function it returns me the number as hh:mm:ss<BR><BR>Cheers<BR>Jay

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## No bloat answer:

Function toHMS( secs )<BR> Dim h,m<BR> h = secs 3600<BR> secs = secs MOD 3600<BR> m = secs 60<BR> secs = secs MOD 60<BR> toHMS = h & ":" & m & ":" & secs<BR>End Function<BR><BR>*********<BR><BR>In the unlikely case that the DB you use insists on leading zeroes (e.g., 03:04:07 instead of just 3:4:7), then:<BR><BR>Function toHMS( secs )<BR> Dim h,m<BR> h = Right( "0" & (secs 3600), 2 )<BR> secs = secs MOD 3600<BR> m = Right( "0" & (secs 60), 2 )<BR> secs = Right( "0" & (secs MOD 60), 2 )<BR> toHMS = h & ":" & m & ":" & secs<BR>End Function<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
480

## Wait more bloat required :)

Hi Bill,<BR>Just realised that maybe some of the bloat in my intial post was needed (even if off topic). I&#039;ve been thrust into the world of UDFs in SQL2000 and the function needs to fit into the template for a UDF:<BR>CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST) <BR>RETURNS (return_type_spec) AS <BR>BEGIN <BR>(FUNCTION BODY) <BR>END<BR><BR>Any ideas? I&#039;ve had a go but keep getting syntax errors.

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Okay, some bloat...

I kept thinking that there *must* be a shorter way.<BR><BR>But I don&#039;t really see it. This is shorter code, but really no less "work" for the language to go through:<BR><BR>Function toHMS( secs )<BR> toHMS = (secs3600) & ":" & ((secs MOD 3600)60) & ":" & (secs MOD 60)<BR>End Function<BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Oh, lordy...

...I&#039;m not a SQL person. Aaarrgghh...<BR><BR>Um...<BR><BR>CREATE FUNCTION whatever.toHMS( @secs int )<BR>RETURNS int AS<BR>BEGIN<BR> DECLARE @hr int<BR> DECLARE @mn int<BR> @hr = FLOOR( @secs / 3600 )<BR> @secs = @secs % 3600<BR> @mn = FLOOR( @secs / 60 )<BR> @secs = @secs % 60<BR> RETURN( CONVERT(VARCHAR,@hr) + ":" + CONVERT(VARCHAR,@mn) + ":" + CONVERT(VARCHAR,@secs) )<BR>END<BR><BR>**************<BR><BR>But PLEASE don&#039;t hold me to that!

7. Senior Member
Join Date
Dec 1969
Posts
96,118

## WAIT A MINUTE! ALL WRONG!

Your "no bloat" message tossed out the most important part(s) of what you are trying to do!<BR><BR>This is a heluva lot simpler than that!<BR><BR>Just use DATEADD!<BR><BR> @fullDateTime = DATEADD( &#039;s&#039;, @numseconds, @dateOnly )<BR><BR>PRESTO!<BR><BR>

8. Senior Member
Join Date
Dec 1969
Posts
480

## RE: Okay, some bloat...

Looks great Bill,<BR>Alas the function won&#039;t fit into the Sql2000 UDF template if you see what I mean. That has a slightly different syntax to a normal VB function. I should really post this in the database forum to save the ASP&#039;ers bandwidth.<BR>The format for a UDF is:<BR>CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST) <BR>RETURNS (return_type_spec) AS <BR>BEGIN <BR>(FUNCTION BODY) <BR>END<BR><BR>Use now, learn later seems to be the motto at work lately!

9. Senior Member
Join Date
Dec 1969
Posts
96,118

## HA! I gave you *TWO* answers...

...in the time it took you to tell me I was wrong. &#060;snicker /&#062;<BR><BR>

10. Senior Member
Join Date
Dec 1969
Posts
946

## sheesh... <eop>

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•