Sum between two datepickers in sql

Question

0.00/5 (No votes)

See more:

Hi i have a table in a database with the following data

startdate | seconds
----------------------
26/06/2014 3
26/06/2014 3
26/06/2014 4
25/06/2014 2
25/06/2014 52
25/06/2014 6

I have 2 date pickers, called datetimepicker1 and datetimepicker2.

I've tried to sum the seconds on a certain date but i keep getting error's, i've looked on a few forums and people suggested using "cast", but i've had no luck.

here's my button code, and hopefully to display the seconds in a textbox called labelsum.

VB

        Dim temp_sum As String

       con.ConnectionString = "Data Source=03-gbnew-hddev;Initial Catalog=DPMO_Dev;Integrated Security=True"
con.Open()
cmd.Connection = con

              cmd.CommandText = "SELECT sum(cast(seconds as int)) from [sqlccmlinestatus] WHERE (cast(startdate as int) >= " & DateTimePicker1.Value.Date & " and (cast(startdate as int) <= " & DateTimePicker2.Value.Date & ""


Dim lrd As SqlDataReader = cmd.ExecuteReader()

lrd.Read()

temp_sum = lrd("seconds").ToString()
labelsum.text = temp_sum

con.Close()

Any help would be brilliant.

Many Thanks,
Pete

Posted 25-Jun-14 22:21pm

PeterHall

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-06-25T22:41:00

Solution 1

If you need CAST, then that implies you stored the number of seconds as a string - which is a poor idea as it means you can't use the values directly. And if you need to cast the start date, than that's potentially even worse as the cast could fail, depending on what is stored in the DB.
So change your DB, store the date as a DATETIME value, and the seconds as an INT
Your SQL then becomes pretty trivial:

VB

cmd.CommandText = "SELECT SUM(Seconds) FROM MyTable WHERE startDate BETWEEN @BEG AND @END"
cmd.Parameters.AddWithValue("@BEG", DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@END", DateTimePicker2.Value.Date)
temp_sum = cmd.ExecuteScalar()

Posted 25-Jun-14 22:41pm

OriginalGriff

Comments

PeterHall 26-Jun-14 11:13am

Worked brilliantly, quick question, that's all working now summing the seconds with the beginning and end date, but how would I add the amount of seconds in a certain group.

E.g. i've expanded my db to include a "status" column, so each row would show.

Date Status Seconds
----------------------------------------
26/06/14 Running 3
26/06/14 Break 5
26/06/14 Running 8
26/06/14 Repair 2

How would I count each one separately?

I've tried to count just one just typing the status i want to count into "runningl" but with no luck with the code below:

cmd.Parameters.Clear()
cmd.CommandText = "SELECT SUM(seconds) FROM sqlccmlinestatus WHERE status @status1 and startdate BETWEEN @BEG AND @END"
cmd.Parameters.AddWithValue("@status1", runningl.Text)
cmd.Parameters.AddWithValue("@BEG", DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@END", DateTimePicker2.Value.Date)
runningseconds = cmd.ExecuteScalar()
runninglabel.Text = runningseconds

Many Thanks,
Pete