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

satabeac...

SOLVED CLOSED ACTIVE ANSWERED
User: satabeach
Posted: on Sep 20, 2018 03:36 AM
Forum: SQL Server
Answer: 1
Views: 3181

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

nagaraju...

0 2

Reply
ANSWER
User: nagaraju60
Replied: on Sep 20, 2018 05:04 AM
Report

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;

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

Company

Explore

Follow us

Contact Us