How to Sum the time in Varchar column in SQL Server

We are going to achieve this using the Inbuilt functions in SQL i.e Convert, DateAdd and DatePart.

First of all I create a table with 2 columns, one is to store time and another for date but instead of datetime format, I am saving it in varchar columns.

Table script:

create table testdate(
TimeField varchar(50),
DateField varchar(50)
)

Then am inserting the following records into the table.

insert into testdate values('00:34:30 AM','5/6/2012')
insert into testdate values('01:45:40 AM','5/6/2012')
insert into testdate values('07:01:00 AM','5/6/2012')
insert into testdate values('4:01:00 PM','5/6/2012')

Now we need to sum the TimeField column in the above table. We are going to do it in single query but there are 4 steps inside that.

  1. Convert Timefield to datetime.
  2. Convert hours , minutes to seconds in the converted datetime.
  3. Sum the seconds.
  4. Convert seconds back to hour format.

The below query will do the above four steps and return the sum of the hours.

select convert(char(8),dateadd(second,SUM ( DATEPART(hh,(convert(datetime,TimeField,1))) * 3600 +
DATEPART(mi, (convert(datetime,TimeField,1))) * 60 + DATEPART(ss,(convert(datetime,TimeField,1)))),0),108)
FROM testdate where DateField='5/6/2012'


the output will be

(No column name)
20:22:10

Ebook Download
View all
Learn
View all