sql中两个日期选择器之间的总和 [英] Sum between two datepickers in sql

问题描述

嗨我在数据库中有一个包含以下数据的表格



startdate |秒

----------------------

26/06/2014 3

26/06/2014 3

26/06/2014 4

25/06/2014 2

25/06/2014 52

25/06/2014 6



我有2个日期选择器，名为datetimepicker1和datetimepicker2。



我试图在特定日期累计秒数，但我一直得到错误，我看了几个论坛，人们建议使用演员，但我没有运气。



这是我的按钮代码，希望在名为labelsum的文本框中显示秒数。



< pre lang =vb> Dim temp_sum As String

con.ConnectionString = 数据源= 03-gbnew-hddev;初始值目录= DPMO_Dev;集成安全性=真
con.Open（）
c md.Connection = con

cmd.CommandText = SELECT sum（cast（秒为int） ））来自[sqlccmlinestatus] WHERE（cast（startdate as int）> =& DateTimePicker1.Value。日期& 和（cast（startdate as int）< =& DateTimePicker2.Value。日期&


Dim lrd As SqlDataReader = cmd.ExecuteReader（）

lrd.Read（）

temp_sum = lrd（ seconds ）.ToString（）
labelsum.text = temp_sum

con.Close（）

任何帮助都会很棒。

很多谢谢，
Pete

解决方案

如果你需要CAST，那么这意味着你将秒数存储为一个字符串 - 这是一个糟糕的主意，因为它意味着你不能直接使用这些值。如果你需要转换开始日期，那么可能更糟糕的是因为演员可能会失败，这取决于数据库中存储的内容。

因此，更改数据库，将日期存储为DATETIME值，将秒数存储为INT

然后，您的SQL变得非常简单：

 cmd.CommandText =   SELECT SUM（Seconds）FROM MyTable WHERE startDate BETWEEN @BEG AND @END 
 cmd.Parameters.AddWithValue（  @ BEG，DateTimePicker1.Value。日期）
 cmd.Parameters.AddWithValue（  @ END，DateTimePicker2.Value。日期）
 temp_sum = cmd.ExecuteScalar（）

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.

        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

解决方案

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:

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()

这篇关于sql中两个日期选择器之间的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！