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

Jagjit Saini

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes15y ago
Accepted 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 Saini15y ago
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 MySqlmyCommand = 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
Vulpes15y ago
To display it in 24 hour format try:

string date = _dt.ToString("dd/MM/yyyy HH:mm:ss");