Getting the current year in an sql query

Hello experts,

I am working on an application in C#.
 On one of its forms I have an sql query which retrieves data from an SQL Server database table. The query works fine in its present form which is listed below.
  Basically it counts certain records in a specified month.
However, the application was created in 2011 and the way the query was made doesn’t take account of the year. That was OK when there was only one year (2011) but now that we are moving into 2012 there will be more than a single year, which means the query has to identify the current year when it retrieves the data for a certain month.
I tried to modify it to the following line but it is not working – can you help?

comm = new SqlCommand("selecte count(*) from Main where  year(dDate)=year(Getdate())And tType=@tType And mMonth=@mMonth And sSubject Like @sSubjec", con);

Please note that dDate is a column of the Date data type.

 

public void getCalls(string theMonth, string theSubject)
        {
            SqlConnection con = default(SqlConnection);
            con = new SqlConnection("Server=PC;Database=HelpDesk;integrated security=True");
            SqlCommand comm = default(SqlCommand);
            comm = new SqlCommand("SELECT COUNT(*) FROM Main where tType=@tType And mMonth=@mMonth And sSubject LIKE @sSubject", con);
            comm.Parameters.AddWithValue("@mMonth", System.Data.SqlDbType.NVarChar);
            comm.Parameters["@mMonth"].Value = theMonth;
            comm.Parameters.AddWithValue("@tType", System.Data.SqlDbType.NVarChar);
            comm.Parameters["@tType"].Value = "Call";
            comm.Parameters.AddWithValue("@sSubject", System.Data.SqlDbType.NVarChar);
            if (theSubject == "wa")
            {

                comm.Parameters["@sSubject"].Value = "wa%";
                con.Open();
                CallsWA = Convert.ToInt32(comm.ExecuteScalar());
                con.Close();
            }

            if (theSubject == "test")
            {
                comm.Parameters["@sSubject"].Value = "test%";
                con.Open();
                CallsTest = Convert.ToInt32(comm.ExecuteScalar());
                con.Close();
            }

            if (theSubject == "email")
            {
                comm.Parameters["@sSubject"].Value = "email%";
                con.Open();
                CallsEmail = Convert.ToInt32(comm.ExecuteScalar());
                con.Close();
            }
            if (theSubject == "BB")
            {
                comm.Parameters["@sSubject"].Value = "BB%";
                con.Open();
                CallsBB = Convert.ToInt32(comm.ExecuteScalar());
                con.Close();
            }
            if (theSubject == "Other")
            {
                comm.Parameters["@sSubject"].Value = "Other%";
                con.Open();
                CallsOther = Convert.ToInt32(comm.ExecuteScalar());
                con.Close();
            }

Open in new window

adamtraskAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

the_endjinnCommented:
GetDate(yyyy) Should give you the current year, or GetDate(yy) for short year date
0
the_endjinnCommented:
Or ignore me as I'm talking nonsense and wasn't paying attention.

DATENAME(YEAR, GETDATE())

Open in new window

0
santhimurthydCommented:
To get the current year use the below SQL

DatePart(YYYY,Getdate())

In your existing SQL, your having an separate column (mMonth) to mention the month from the SQL below

SELECT COUNT(*) FROM Main where tType=@tType And mMonth=@mMonth And sSubject LIKE @sSubject

and your using the dDate, which have the full datetime and to get the year part Datepart(YYYY,dDate) neess to use and the final SQL will be as

FINAL SQL

SELECT COUNT(*) FROM Main where tType=@tType And mMonth=@mMonth  and Datepart(YYYY,dDate) = DatePart(YYYY,Getdate()) And sSubject LIKE @sSubject
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

adamtraskAuthor Commented:
Thank you very much santhimurthyd:
0
adamtraskAuthor Commented:
Explained very well
0
santhimurthydCommented:
Thank you adam
0
Alfredo Luis Torres SerranoASP .Net DeveloperCommented:
Yuor query seems to be fine only add a space between the And

comm = new SqlCommand("selecte count(*) from Main where  year(dDate)=year(Getdate()) And tType=@tType And mMonth=@mMonth And sSubject Like @sSubjec", con);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.