CODEWITHSUNDEEP
sqlasp.netdatetimesql-server-2008-r2
Anita Mathew
Anita Mathew

Reputation: 183

Date being saved in the database in the wrong format

We are trying to save the current date and time in our database in the Datetime format . But the value entered always shows up as 1900-01-01 00:00:00.000.

we tried this code :

  SqlCommand cmd = new SqlCommand("update " + 
                                   con.Database + 
                                 " set Teacher_code=@Teacher_code ,Checkdate=" + 
                                   DateTime.Now.ToShortDateString() + 
                                 " where copycode=@copycode", con);

How to fix this issue?

Upvotes: 0

Views: 704

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82524

Never use string representation of DateTime to send date/time values from the .Net framework to SQL Server. The DateTime struct maps directly to SQL Server various date/datetime data types, so whenever you need to pass a date/time value from your .Net code to SQL Server, simply pass an instance of the DateTime struct as a parameter to the SQL Statement.

That being said, assuming your database server is on the same clock as your application server, there is no reason to pass DateTime.Now at all - simply use GetDate() for DateTime or SysDateTime() for DateTime2:

Also, please note that update statements are targeting tables, while con.Database seems to suggest a database name. I think a better code would probably be: 

SqlCommand cmd = new SqlCommand("update <TableName>" + 
                               " set Teacher_code = @Teacher_code ,Checkdate = GETDATE()"+ 
                               " where copycode = @copycode", con);

(replace <TableName> with your actual table name, of course)

Upvotes: 1

pankaj singh
pankaj singh

Reputation: 105

Correct the data type for the column Checkdate from datetime to date. Date data type only store the date part. It will work in your case.

Upvotes: 0

Onga Leo-Yoda Vellem
Onga Leo-Yoda Vellem

Reputation: 296

This is a DBNull default value for date inserted into your database. It means that a null value is being saved as date.

Please read this for better explainations and on how to resolve the issue: datetime issue with 01/01/1900

Upvotes: 0

Ravi Mungalpara
Ravi Mungalpara

Reputation: 14

.ToShortDateString() will return date only so you cannot be able to save time. Go with DateTime.Now or DateTime.UtcNow for universal time.

Upvotes: 0

Related Questions