Select current date and display results sql

Question

I am a newbie into this and I don't know how to do this.

I have SQUL Server database, where I have records for many dates. It is like this:

id | run | date
1  | 12  | 02.22.2014 14:23:34
2  | 22  | 02.22.2014 18:20:25
3  | 05  | 02.22.2014 20:58:30
4  | 08  | 02.23.2014 08:28:44
5  | 10  | 02.23.2014 12:53:28
6  | 18  | 02.23.2014 14:23:34

What I need is to give a list of all dates, no repeat, and show them in a listbox, using winforms in C#. For instance, in this case I would have just 02.22.2014 and 02.23.2014. In that case, if I select 02.22.2014 I would like to calculate the average 'run' (in this case is 13) and show it in a label.

What is the query I should use and how can I populate the listbox with the dates? What I've read for mysql there is a query like WHERE DATE('Date')=CURDATE().

Are you using WPF? or winforms? or even web like asp.net mvc ? — Tan, Feb 25 '14 at 09:16
What database engine do you use. SQL Server, oracle,... Depending on the database engine, there are different functions to group by date — mnieto, Feb 25 '14 at 09:17

score 1 · Answer 1 · answered Feb 25 '14 at 09:23

1

This query should have it:

select 
    avg(run) 
from 
    tableName 
group by DATEPART(year, [date])
        ,DATEPART(month, [date])
        ,DATEPART(day, [date])

answered Feb 25 '14 at 09:23

crthompson

15,653
6
58
80

Tan · Answer 2 · 2014-02-25T09:29:48.683

1

SELECT AVG(run), CAST(date AS DATE) FROM TABLENAME
GROUP BY CAST(date AS DATE)

Then you will get the result something like this

13 02-22-2014
Next avg and date and so on.

Then you map the results with your listbox.

edited Feb 25 '14 at 09:29

answered Feb 25 '14 at 09:24

Tan

2,148
3
33
54

This is a good answer, tho, you'll have to cast the first `date` as DATE as well as in the group by to get the result you show. – crthompson Feb 25 '14 at 09:28
@paqogomez yes that is true. Missed that one. – Tan Feb 25 '14 at 09:29
I like this answer better than mine. +1. Hopefully i'll get some points too. :) – crthompson Feb 25 '14 at 09:30
thanks, how can i populate the listbox now? shall i create a list of object first? – user2962759 Feb 25 '14 at 09:41
@user2962759 Yes you can map the sql result to a object then make a list of that object. Then map it into listbox. Then when selecting a listbox get that selecteditem and take the avg value and populate it to your label /textbox. if you dont know how to populate the listbox you can google for it. There a ton of examples. – Tan Feb 25 '14 at 09:44
@user2962759 here is an example how to bind the list to a listbox.http://stackoverflow.com/questions/2675067/binding-listbox-to-listobject – Tan Feb 25 '14 at 09:48

score 0 · Answer 3 · answered Feb 25 '14 at 09:30

To fill the list:

string sql = "SELECT DISTINCT CONVERT(VARCHAR, [DATE], 105) FROM TableName";

To get de average:

string sql = "SELECT AVG(run) FROM TableName WHERE CONVERT(VARCHAR, [DATE], 105) = @SelectedDate";

Please, review the documentation for the Convert function for different date/time formats conversion.

Select current date and display results sql

3 Answers3