How to Convert C# Variable DateTime Into SqlDbType.DateTime

Question

How to convert a C# DateTime variable into Enum of SqlDataType.DateTime?

How to consume that enum into a connection string?

Is doing something like this correct?

string str = "SELECT * FROM TABLE WHERE CreateDt " + <that enum>;
SqlConnection Connection = new SqlConnection (<connection setting>);
Table = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(str, Connection);
adapter.FillSchema(Table, SchemaType.Source);
adapter.Fill(Table);

Thank you

competent_tech · Accepted Answer · 2012-01-06 02:20:54Z

3

Your best option is to use a parameterized command:

            var cmd = new SqlCommand();
            cmd.Connection = conn;

            DateTime MyDate = DateTime.Now;

            cmd.CommandText = @"SELECT * FROM TABLE WHERE CreateDt = @MyDate";
            cmd.Parameters.AddWithValue("@MyDate", @MyDate);

            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = cmd;
            adapter.FillSchema(Table, SchemaType.Source);
            adapter.Fill(Table);

answered Jan 6, 2012 at 2:20

competent_tech

45.1k12 gold badges96 silver badges114 bronze badges

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

6 Comments

rofans91 Over a year ago

is this command text allows alteration for example doing sth like cmd.CommandText += "AND FinishDt = @MyDate";

rofans91 Over a year ago

if i use Command Text, how shall I fill a datatable using that connection? (like the 1 I use show in my question)

competent_tech Over a year ago

Sorry, I was getting the correct syntax for that. I have updated the answer.

rofans91 Over a year ago

I receive sqldatetime overflow error when i test this code, any idea how it happen?

competent_tech Over a year ago

This will definitely happen if the date/time you are passing has not been set to a valid value. This is because in this case, the DateTime parameter will default to DateTime.MinValue (1/1/0001), which is far below the allowable minimum in SQL Server, which is 1/1/1753.

|

Garett · Accepted Answer · 2012-01-06 02:34:35Z

1

This is not tested, but how about:

string str = "SELECT * FROM TABLE WHERE CreateDt = @createDate";

SqlConnection Connection = new SqlConnection (<connection setting>);

Table mytable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(str, Connection);

adapter .SelectCommand.Parameters.Add("@createDate", SqlDbType.DateTime)
adapter .SelectCommand.Parameters("@createDate").Value = <Some DateTime>

adapter.FillSchema(mytable, SchemaType.Source);
adapter.Fill(Table);

answered Jan 6, 2012 at 2:34

Garett

16.9k5 gold badges58 silver badges63 bronze badges

Collectives™ on Stack Overflow

How to Convert C# Variable DateTime Into SqlDbType.DateTime

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related