Converting C# DateTime to T-SQL Time in SelectCommand

Question

I'm trying to return a query (in a gridview in ASP.NET) WHERE Time >= DateTime.Now.Add(-60). The WHERE clause has been giving me no end of difficulties.

DateTime pastTime = DateTime.Now.Add(-60);    
ds_DB.SelectCommand = "SELECT * FROM [vPurchaseTotals] WHERE [TimeOfTransaction] >=  " + pastTime;

My issue is getting pastTime to convert properly, so it only returns the newer data. [TimeOfTransaction] is a time(7) data type in the table.

How do I parse C#'s DateTime to SQL Server's Time?

Use a parameterized query and not string concatenation. That will not only fix your problem but it will prevent against sql injection (when using string types) as well as improve sql servers query cache plan so possible performance boost. See Microsoft article How to: Execute a Parameterized Query — Igor
– Igor, Commented Apr 19, 2016 at 19:43
You don't. Pass a parameter instead, of the correct type, so no fiddling with strings is necessary. What is ds_DB.SelectCommand -- does it allow parameterization? If not, why not? — Jeroen Mostert
– Jeroen Mostert, Commented Apr 19, 2016 at 19:43
Okay, I will look into parameterization. ds_DB.SelectCommand is a command against the bound datasource of my database. — Waffles
– Waffles, Commented Apr 19, 2016 at 19:53

Matthew Alltop · Accepted Answer · 2016-04-19 21:14:42Z

1

Here, try this:

using(SqlConnection conn = new SqlConnection(yourConnectionString))
{
    DateTime pastTime = DateTime.Now.Add(-60); 

    ds_DB.SelectCommand = @"SELECT * FROM [vPurchaseTotals]
                            WHERE [TimeOfTransaction] >= @PastTime";

    SqlCommand cm = conn.CreateCommand();
    cm.CommandText = ds_DB.SelectCommand;

    cm.Parameters.Add("@PastTime", SqlDbType.Time).Value = pastTime.TimeOfDay; //For comparison with TSQL TIME type

    try
    {
        conn.Open();
        // Do what you need to do here.
    }
    catch(SqlException e)
    {
        // Handle Exception
    }
    finally
    {
        conn.Close();
    }
}

Just for future reference, you should always parameterize your queries. It ends up being a lot safer and cleaner/easier to read and adjust.

EDIT: Are you using a SqlDataAdapter class? Is that what ds_DB is an instance of? I would personally just use a string value for your query and then implement the SqlDataAdapter like this:

try
{
     conn.Open();

     using(SqlDataAdapter da = new SqlDataAdapter(cm))
     {
          da.Fill(DataTable dt);
     }
}

edited Apr 19, 2016 at 21:14

answered Apr 19, 2016 at 20:03

Matthew Alltop

5454 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results...Also - if the datatype in the database is TIME(7), I'd suggest using SqlDbType.Time (not SqlDbType.DateTime) for your parameter

Waffles Over a year ago

So the issue I'm having with understanding parameterization, is that I'm using a datasource that's bound to a gridview control in asp.net. I'm having trouble figuring out how to get an open SQL connection to be bound to a data control.

Matthew Alltop Over a year ago

@marc_s Thanks for those details; that's a helpful bit of information to have; updated to reflect that.

Collectives™ on Stack Overflow

Converting C# DateTime to T-SQL Time in SelectCommand

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related