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?
 
santhimurthydConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
adamtraskAuthor Commented:
Thank you very much santhimurthyd:
0
 
adamtraskAuthor Commented:
Explained very well
0
 
santhimurthydCommented:
Thank you adam
0
 
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.