Geek Answers Handbook

Mysql date format using c #

I need to find some data in mysql record regarding date values ​​using C # code. I use the following code in C #, but I can not find the entries. Please help me.

My C # code:

Select distinct v.* from Ven v inner join Des d on v.venid=d.venid and cast( d.despdate as datetime) between cast('" + dTime1.Value.ToString("dd-MMM-yyy") + "' as datetime) and cast('" + dTime2.Value.ToString("dd-MMM-yyy") + "'as datetime)

My Mysql table entry:

enter image description here

+1
source share
3 answers

You are mistaken in trying to include the value directly in your SQL query to get started. Use parameterized SQL and specify the value as DateTime , and if your database table also uses the date / datetime field (this should be), you should be fine.

You should avoid including values ​​directly in your SQL for three reasons:

  • As a result, you will encounter unpleasant string conversions that may use inappropriate formats, etc. This is the problem here.
  • You invite SQL injection attacks
  • You mix code and data, which makes it hard to read code and harder to check data.

You need something like:

 string sql = @"Select distinct v.* from Ven v inner join Des d on v.venid=d.venid and cast(d.despdate as datetime) between @start and @end"; using (MySqlCommand command = new MySqlCommand(connection, sql)) { command.Parameters.AddWithValue("@start", startDateTime); command.Parameters.AddWithValue("@end", endDateTime); // Execute the command here }

If Des.despdate not a suitable data type, you should change this ...

+2
source

convert d.despdate column to DATE with STR_TO_DATE

 SELECT ... FROM ... WHERE STR_TO_DATE(d.despdate, '%d-%b-%Y') BETWEEN '2013-01-15' AND '2013-01-30'

to improve performance, parameterized query using the MySQLCommand object. ex

 string connStr = "connection string here"; string query = @"SELECT ... FROM... WHERE STR_TO_DATE(d.despdate, '%d-%b-%Y') BETWEEN @date1 AND @date2" using(MySqlConnection _conn = new MySqlConnection(connStr)) { using (MySqlCommand comm = new MySqlCommand()) { comm.Connection = conn; comm.CommandText = query; comm.Parameters.AddWithValue("@date1", dTime1.Value); comm.Parameters.AddWithValue("@date2", dTime2.Value); try { conn.Open(); comm.ExecuteNonQuery(); } catch(MySqlException e) { // do something with // e.ToString() } } }
+1
source

It should be yyyy

  Select distinct v.* from Ven v inner join Des d on v.venid=d.venid and cast( d.despdate as datetime) between cast('" + dTime1.Value.ToString("dd-MMM-yyyy") + "' as datetime) and cast('" + dTime2.Value.ToString("dd-MMM-yyyy") + "'as datetime)
0
source

More articles:


All Articles