Link to home
Create AccountLog in
Avatar of adamtrask
adamtrask

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
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

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
Avatar of sammySeltzer
sammySeltzer
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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.
Avatar of Rick
Rick

select column1, column2, year(dDate) as 'Date'  from yourTable
That is the simplest solution and that's what I suggested.
Oops... yes, Sammy should get the points.
Avatar of adamtrask

ASKER

Thanks to all of you