SQL Server Error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

Last Reply one year ago By nagaraju60

Posted one year ago

When I run the below code I got the below error 

Msg 242, Level 16, State 3, Line 8 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

 

I want get the total of TotalOT

DECLARE  @Table AS TABLE (Id INT, totalOT VARCHAR(5))

INSERT INTO @Table (Id, totalOT) VALUES (1, '114:06')
INSERT INTO @Table (Id, totalOT) VALUES (2, '56:56')
INSERT INTO @Table (Id, totalOT) VALUES (3, '74:46')


SELECT CAST((SUM (DATEPART(HOUR, CONVERT (VARCHAR, totalOT, 108))) +
(SUM(DATEPART(MINUTE, CONVERT (VARCHAR, totalOT, 108)))/60) ) AS VARCHAR(2)) + ':' +
CAST(SUM(DATEPART(MINUTE, CONVERT (VARCHAR, totalOT, 108))) - 60 * (SUM(DATEPART(MINUTE, CONVERT (VARCHAR, totalOT, 108)))/60)
 AS VARCHAR(2))
 FROM @Table

 

Posted one year ago

Dear @satabeach,

  please refer below query

DECLARE  @Table AS TABLE (Id INT, totalOT VARCHAR(6)) 
INSERT INTO @Table (Id, totalOT) VALUES (1, '114:06')
INSERT INTO @Table (Id, totalOT) VALUES (2, '56:56')
INSERT INTO @Table (Id, totalOT) VALUES (3, '74:46');

 WITH    cte
          AS ( SELECT   SUM(CAST(SUBSTRING(totalOT, 1, CHARINDEX(':', totalOT) - 1) AS INT)) AS Hours ,
                        SUM(CAST(SUBSTRING(totalOT, CHARINDEX(':', totalOT) + 1, 5) AS INT)) AS Minutes
               FROM     @Table
             )
    SELECT  CAST(Hours + Minutes / 60 AS NVARCHAR(20)) + ':' + CAST(Minutes % 60 AS NVARCHAR(20)) AS Duration
    FROM    cte;