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

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);

Collectives™ on Stack Overflow

How to Convert C# Variable DateTime Into SqlDbType.DateTime

2 Answers 2

6 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Related