count data from sql

Question

0.00/5 (No votes)

See more:

I need to count data from sql
It's ok, but there is a problem.

My data is like this on my table:

coldate (varcharmax) (data comes datetimepicker short format)
23.02.2010
30.04.2010
15.04.2010

How do I count by month from sql?

I tried this: select * from datepart(month, coldate)=04

but it doesnt work.

I need to count by month and to add result to label1.text

What should I do?

Posted 25-Oct-10 5:07am

luthier

Updated 25-Oct-10 5:10am

Dalek Dave

v2

Add a Solution

Comments

Dalek Dave 25-Oct-10 11:10am

Edited for Grammar and Readability.

Yusuf 25-Oct-10 11:28am

What are you using varchar for your date? Why can't you use Date?

6 solutions

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)

TheyCallMeMrJames · Answer 1 · 2010-10-25T05:40:00

Solution 1

You are probably looking for GROUP BY and something to differentiate between months in different years.

This solution covers both scenarios (where you care about the year, and where you don't): SQL Hacks Solution[^]

Cheers.

Posted 25-Oct-10 5:40am

TheyCallMeMrJames

Updated 25-Oct-10 5:44am

v2

William Winner · Answer 2 · 2010-10-25T06:12:00

How about this...

If I have a table called Table1 with the following structure:

CurrentDate

2/5/2010

3/6/2009

3/7/2008

4/5/2010

6/5/2010

7/5/2009

2/7/2001

2/9/2010

and I run the following SQL Statement:

SQL

SELECT Part1.MyMonth, Count(Part1.MyMonth) AS CountOfMyMonth
FROM (SELECT Month(CurrentDate) AS MyMonth
      FROM Table1) AS Part1
GROUP BY Part1.MyMonth;

I get:

MyMonth	CountOfMyMonth
2	3
3	2
4	1
6	1
7	1

basementman · Answer 3 · 2010-10-25T06:28:00

Solution 3

select
Substring(coldate,7,4) + '-' + Substring(coldate,4,2),
COUNT(*)
from
TableName
group by
Substring(coldate,7,4) + '-' + Substring(coldate,4,2)
order by 1

Posted 25-Oct-10 6:28am

basementman

luthier · Answer 4 · 2010-10-25T06:57:00

I am not sure but, we dont have to use group by... why we use? I need to know for example 05 (may) how many times repeat? Two times, hundres times... i need one value, that is count...

My problem is, how i get, may from that value 30.05.2010...

William thanx for answer but i dont want to each month mod...

my table context product orders...

I want to learn; how many product ordered "january, february, march, april" in "2010"?

in your table
2/5/2010
3/6/2009
3/7/2008
4/5/2010
6/5/2010
7/5/2009
2/7/2001
2/9/2010

first value day or month?

and i seperate mark is point for my date format...

day.month.year

i need to count product order by month...

Simon_Whale · Answer 5 · 2010-10-26T04:32:00

Solution 5

select month(cast(coldate as datetime))  as [month], count(*)
from tablename
group by month(cast(coldate as datetime))
order by [month] asc

Posted 26-Oct-10 4:32am

Simon_Whale

luthier · Answer 6 · 2010-10-26T06:36:00

Solution 6

I solved my problem by my friend Vladimir.

The answer is:

"SELECT count(*) as x from files where (substring(datein,4,2)='01' OR substring(datein,4,2)='02') and substring(datein,7,4)='2010'"

Posted 26-Oct-10 6:36am