How to store and fetch Date and time into a database in a particular format

Question

Hi

datetime abc

string ConnectionStr = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
MySqlDataAdapter da = new MySqlDataAdapter("select now()", ConnectionStr);
DataTable dt = new DataTable();
da.Fill(dt);

_dt = (DateTime)dt.Rows[0][0];
abc = _dt;

It display value as 05/09/11 06:09:00 PM , but i don't want to store PM

Thanks

Vulpes · Answer

If you're inserting into MySql I'd use the yyyy-MM-dd HH:mm:ss format and surround it with single quotes or (better) use parameters: MySqlCommand = new MySqlCommand("Insert into attendance (attendance_id,employee_id,datetime) values(" + 10 + "," + empno + ",'" + _dt.ToString("yyyy-MM-dd HH:mm:ss") + "')", con);

Jagjit Saini · Answer

Hi datetime _dt; string ConnectionStr = ConfigurationManager.ConnectionStrings["cnn"].ConnectionString; MySqlDataAdapter da = new MySqlDataAdapter("select now()", ConnectionStr); DataTable dt = new DataTable(); da.Fill(dt); _dt = (DateTime)dt.Rows[0][0]; datetime column is datetime datatype . Database is MySql myCommand = new MySqlCommand ( "Insert into attendance (attendance_id,employee_id,datetime) values(" + 10 + "," + empno + "," + _dt.ToString( "dd/MM/yyyy HH:mm:ss" ) + ")" , con); MySqlCommand It is giving error on DateTime column : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18:33:45,NULL,NULL,2)' at line 1 Thanks

Vulpes · Answer

To display it in 24 hour format try: string date = _dt.ToString("dd/MM/yyyy HH:mm:ss");