We help IT Professionals succeed at work.

Trying to count the number of records where date column = 2010

Hello,

Now I am trying to count the number of records where the date column (dDate) contains 2010.
The error I am getting is a "clash" between date and integer.

I am used the following select count statement as listed below:


mySqlcommand = New Sqlcoomand("Select count(*) where dDate = @dDate",con)
comm.Parameters.AddWithValue("@dDate", System.Data.SqlDbType.Date)
comm.Parameters("@dDate").Value = curYear
 Dim count As Integer
        con.Open()
        count = comm.ExecuteScalar
        Me.txtMonthTests.Text = count.ToString
        con.Close()

Open in new window

Comment
Watch Question

Director, Information Systems
CERTIFIED EXPERT
Commented:
Is dDate an integer, varchar or datetime column?

Regardless, you can either CAST dDate to the appropriate data type to match @dDate or vice versa.

Author

Commented:
It's a date column
CERTIFIED EXPERT
Commented:
Well judging from the naming convention, I'm guessing that curYear is an integer variable, not a date, containing only 2010.
In which case you need to get only the year part of the dDate in the database to do the comparison:


mySqlcommand = New Sqlcoomand("Select count(*) where datepart(dd, dDate) = @dDate",con)
comm.Parameters.Add("@dDate", System.Data.SqlDbType.Int)
comm.Parameters("@dDate").Value = curYear
 Dim count As Integer
        con.Open()
        count = comm.ExecuteScalar
        Me.txtMonthTests.Text = count.ToString
        con.Close()


or optinally using .AddWithValue:

mySqlcommand = New Sqlcoomand("Select count(*) where datepart(dd, dDate) = @dDate",con)
comm.Parameters.AddWithValue("@dDate", curYear)
Dim count As Integer
        con.Open()
        count = comm.ExecuteScalar
        Me.txtMonthTests.Text = count.ToString
        con.Close()
Lara FEA
CERTIFIED EXPERT
Commented:
If you need extract year from date column you need "year". dd will extract day value from dDate column
and I think you need specify table from which you need to get count

select count(*) from myTable where datepart(year, dDate) =2010

Author

Commented:
Thank you all

Explore More ContentExplore courses, solutions, and other research materials related to this topic.