Monday, March 23, 2009

Sum or time Over 24 Hours or Hrs Or Sum of Time Greater than 24 Hrs or Hours

Original Link



DECLARE @Sample TABLE
(
data TIME
)

INSERT @Sample
SELECT '23:30' UNION ALL
SELECT '22:30' UNION ALL
SELECT '16:00'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM
( SELECT ABS(SUM(
CASE CHARINDEX(':', CAST(data AS VARCHAR))
WHEN 0
THEN 0
ELSE 3600 * LEFT(CAST(data AS VARCHAR), CHARINDEX(':', CAST(data AS VARCHAR)) - 1)
END)) AS theHours,
ABS(SUM(
CASE CHARINDEX(':', CAST(data AS VARCHAR))
WHEN 0
THEN 0
ELSE 60 * SUBSTRING(CAST(data AS VARCHAR), CHARINDEX(':', CAST(data AS VARCHAR)) + 1, 2)
END)) AS theMinutes
FROM @Sample
) AS d


This is only when dataType is Time we have to cast the column to VARCHAR

The above query is rewritten as like this

SELECT CASE
WHEN totalhours IS NOT NULL
THEN Stuff(Convert(CHAR(8),Dateadd(SECOND,totalhours,'19000101'),
8),1,2,Cast(totalhours / 3600 AS VARCHAR(12)))
ELSE NULL
END
FROM (SELECT CASE
WHEN (Abs(Sum(Datediff(SECOND,'00:00',data)))) > 0
THEN Abs(Sum(Datediff(SECOND,'00:00',data)))
ELSE NULL
END AS totalhours
FROM @Sample) AS d

Monday, March 16, 2009

Convert Numeric to Time Convert VarChar to Time


If our input string or numeric does not in the form of HH:MM then

Do the stuff in this link



SELECT

--Adds 2 zeros as the end(it decimal places)

CONVERT(VARCHAR(18), Hours),
-- Which adds 0 to the Fixed precision
-- Took only 5 char from the RIGHT postion


RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5),

--Replace . with :
--It looks like time in the format of hh:mm:ss


REPLACE(RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),

--Convert the varchar format to DATETIME

--CONVERT(DATETIME, REPLACE(RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'), 108)
--CAST(REPLACE(RIGHT( CONVERT(VARCHAR(16),(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108) - DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0)),108), 5),':','.')AS NUMERIC(4,2))

--Cast varchar to DateTime
CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108),

--Cast DateTime to Time

CAST(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108)AS TIME),

--Cast numeric to varchar by replaceing . with : and cast that to time and sum the time
CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0) AS TIME),


--Subtract a time from another time

CONVERT(CHAR(5), CAST(

CAST(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108)AS TIME)

AS DATETIME) - CAST(

CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0) AS TIME)
AS DATETIME), 108)


Add '0' to Fixed precision and add '0' to scale numbers


DECLARE @PadChar CHAR(1)
DECLARE @FinalLength INTEGER
DECLARE @SourceString VARCHAR(19)
SET @PadChar = '0'
SET @FinalLength = 5

--Which Gives 2 decimal Digits

SELECT CONVERT(VARCHAR(10),CONVERT(MONEY,2))

--The follwing which adds Fixed precision(if there is 1 digit,
--it adds 0 to previous of the 1 and gives 01.00 as ouput)
-- The output is in the format of 00.00

SET @SourceString = '2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '2.0'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '0.2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '00.2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '0.20'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SUM of time

SELECT CAST(DATEADD(millisecond,
SUM(DATEDIFF(millisecond,0,CAST(totalhrs AS DATETIME))),0) AS TIME)
FROM TABLE