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.
- Convert Timefield to datetime.
- Convert hours , minutes to seconds in the
converted datetime.
- Sum the seconds.
- 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