asked on

Date query in SqlServer through VB.Net or C#

Hello experts,

In an SqlServer database, the main table contains a date column named dDate:
The date in this columns is stored in the following format: year-month-day.
So the 19th of October 2010 would be stored as 2010-10-19,

Is there a way to query the date column to extract the year. For example if I need all records of the year 2010 how do I query the table using VB.net or C# ?

Thanks

sammySeltzer

Do it in query and then use in your vb or .net code like:

select year(ddate) as ddate from your table.

That will give you only year like 2010.

ASKER CERTIFIED SOLUTION

sammySeltzer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Villegas

if I need all records of the year 2010 how do I query the table using VB.net or C# ?

Hello, if your column is really a sql server Date type (not char or varchar), then you can do this to query your data:

using (SqlConnection myConnection = new SqlConnection("MyConnectionString"))
{
    SqlCommand myCommand = new SqlCommand("", myConnection);
    myCommand.CommandText = "SELECT * FROM MyTable WHERE dDate >= CAST(CAST(@pYear AS VARCHAR(4)) + '0101' AS DATE) AND dDate < CAST(CAST(@pYear + 1 AS VARCHAR(4)) + '0101' AS DATE)";
                
    // Use this parameter to set the year to query
    myCommand.Parameters.Add("@pYear", SqlDbType.Int).Value = 2010;

    myConnection.Open();
    using (SqlDataReader dr = myCommand.ExecuteReader())
    {
        // Read each returned row.
        while (dr.Read())
        {
            // Work with the row data...
        }
    }
}

Open in new window

If your column dDate is indexed the previous code will use it.

Carlos Villegas

if your column is not a native date type, then you can do this to query your data:

using (SqlConnection myConnection = new SqlConnection("MyConnectionString"))
{
    SqlCommand myCommand = new SqlCommand("", myConnection);
    myCommand.CommandText = "SELECT * FROM MyTable WHERE YEAR(CAST(dDate AS DATE)) = @pYear";
                
    // Use this parameter to set the year to query
    myCommand.Parameters.Add("@pYear", SqlDbType.Int).Value = 2010;

    myConnection.Open();
    using (SqlDataReader dr = myCommand.ExecuteReader())
    {
        // Read each returned row.
        while (dr.Read())
        {
            // Work with the row data...
        }
    }
}

Open in new window

But of course, the previous code will do a table scan to return the matched rows.

Rick

select column1, column2, year(dDate) as 'Date' from yourTable

sammySeltzer

That is the simplest solution and that's what I suggested.

Rick

Oops... yes, Sammy should get the points.

adamtrask

ASKER

Thanks to all of you