Myujth

I have a stopwatch project in C#. I will start recording time and store it in my database in a "Timespan" column. I also have a "Timestamp" column which records the date that the time was recorded.

Now I want to have a store procedure to give the program a date and I want my program to search the database and sum up ALL values in every corresponding "Timespan" columns on the given date. In other words If I give it a date I want my program to look up every "Timestamp" column cells and if there is one or more timestamp equal to the given date I want the program to go ahead and check all of their related "Timespan" columns and sum them up.

MY CODE (WHICH ONLY WORKS IF THERE IS ONLY 1 TIMESTAMP VALUE EQUAL TO THE GIVEN DATE - IT YELLS AT ME IF THERE IS TWO OR MORE AND THIS IS MY PROBLEM):

CREATE PROCEDURE SumOnDateReport
 @QueryDate DATETIME
AS
 DECLARE @total_sec INT
 DECLARE @HourTime INT
 DECLARE @MinTime INT
 DECLARE @SecTime INT

 SET @HourTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 1, 2)) 
 FROM InfoTable 
 WHERE TimeStampColumn = @QueryDate)
 SET @MinTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 3, 2)) 
 FROM InfoTable 
 WHERE TimeStampColumn = @QueryDate)
 SET @SecTime = (SELECT CONVERT(INT, SUBSTRING(TimeStringColumn, 5, 2)) 
 FROM InfoTable 
 WHERE TimeStampColumn = @QueryDate)

 SET @total_sec = @HourTime * 3600 + @MinTime * 60 + @SecTime

 INSERT INTO InfoTable(TotalSecColumn) VALUES (null)

 UPDATE InfoTable 
 SET TotalSecColumn = @total_sec
 WHERE TimeStampColumn = @QueryDate

 SELECT TotalSecColumn 
 FROM InfoTable
 WHERE TimeStampColumn = @QueryDate

all of my table columns are NVARCHAR but Timestampcolumn is datetime.

Since it's a DATETIME column - you should refrain from trying to guess the string format (there's no default, system-inherent string representation - so your code would always depend on the language/regional settings) - use the proper DATETIME function to deal with this!!

Change your code (those three SET statement with the CONVERT and SUBSTRING) into something like this:

SELECT 
 @HourTime = SUM(DATEPART(HOUR, TimeStringColumn)),
 @MinTime = SUM(DATEPART(MINUTE, TimeStringColumn)),
 @SecTime = SUM(DATEPART(SECOND, TimeStringColumn))
FROM 
 InfoTable 
WHERE 
 TimeStampColumn = @QueryDate

This should work just fine with multiple rows for the same @QueryDate, too!