Technical Article

Difference between dates with thousands separators

,

This function calculates the time difference between 2 datetimes and displays the results in the following format:
H,HHH,HHH:MM:SS

The return is a 15 character field that is left padded with spaces so that the field becomes right justified.  This makes the colons line up for easy reading.  Commas are automatically added as thousands separators as needed by the number of hours displayed.

Entry of a NULL for either or both input parameters will cause a NULL return.

The function does have an inherent limit of a little over 2.2 Million hours (an SQL limit associated with differential date calculations) which is approximately 250 full years or 1,057 working years (~260 8 hour days per year).

As a reminder, UDFs must be fully qualified with the user name to be found.

Example Usage:
SELECT dbo.udf_ElapsedTime(@StartDateTime,@EndDateTime)

--Jeff Moden

CREATE  FUNCTION dbo.udf_ElapsedTime(@StartDateTime DATETIME, @EndDateTime DATETIME)
  RETURNS VARCHAR(15) AS
BEGIN
  DECLARE @HOURS VARCHAR(12)
      SET @HOURS=CONVERT(VARCHAR(12)
                  ,CONVERT(MONEY
                    ,DATEDIFF(HH,0,@EndDateTime-@StartDateTime)
                   )
                ,1)  --Puts commas in conv. from Money to Varchar
      SET @HOURS=LEFT(@HOURS,LEN(@HOURS)-3) --Drop .00 of hours
   RETURN RIGHT(SPACE(15)
            +@HOURS+RIGHT(
                     CONVERT(VARCHAR(8)
                      ,@EndDateTime-@StartDateTime
                    ,108) -- HH:MM:SS time format
                  ,6) --Grab just right 6 or :MM:SS
         ,15) --Grab just right 15 to right justify result
END

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3 (2)

You rated this post out of 5. Change rating

Related content

Technical Article

Write Reports from SQL to Disk (Even HTML!)

Ever need to write reports out to a folder? I have found that creating output files are SA-WEET! (and easy too) The sample script uses BCP to create an HTML file.This process works well for reports that need to be generated nightly and take too long to run in real time.  Use an SMTP mail […]

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2002-04-18

4,266 reads