We help IT Professionals succeed at work.

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
Comment
Watch Question

Top Expert 2011

Commented:
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.
Top Expert 2011
Commented:
and if you really must do in vb then something like this:

Dim dDate As DateTime = #1/1/2011#
Dim curYear As Int32 = dDate.Year

or simply

ddate.today.year will give you same thing as the query
Carlos VillegasFull Stack .NET Developer

Commented:
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 VillegasFull Stack .NET Developer

Commented:
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.

Commented:
select column1, column2, year(dDate) as 'Date'  from yourTable
Top Expert 2011

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

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

Author

Commented:
Thanks to all of you

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