Insert/Update SQL DateTime From asp.net(C#) System.dateTime

Question

Here is what i am trying to do:

NOTE: I don't want to use parameterized queries.

Code:

 refresh_query.Append("UPDATE Table SET ");
 System.DateTime now = System.DateTime.Now;
      refresh_query.Append("date='" +now + "' ");
      refresh_query.Append("WHERE user_id='1'");

which results in this query:

 UPDATE Table SET date='25/02/2014 12:04:00'
 WHERE user_id='1'

getting the following error:

   An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll 
but was not handled in user code
Additional information: The conversion of a varchar data type to a datetime data 
type resulted in an out-of-range value.

Pretty much every database engine out there has something that returns the current date and time. Why not use that instead of dicking around with passing strings? — Dan Bracuk
– Dan Bracuk, Commented Feb 25, 2014 at 13:03
i am not using parameterized SQL queries, because i am building the query with if statments, i may have date = ... , date2 = ..., date4 = — dyk
– dyk, Commented Feb 25, 2014 at 13:48
@DanBracuk i did put "Now" as example, to show that it is System.dateTime format, but i do some calculations and then i want to put it on database, any suggestions? — dyk
– dyk, Commented Feb 25, 2014 at 13:59
@DanBracuk post your comment as an answer, i will accept it. thanks for the hint — dyk
– dyk, Commented Feb 25, 2014 at 14:07

Dan Bracuk · Accepted Answer · 2014-02-25 14:13:28Z

2

Pretty much every database engine out there has something that returns the current date and time. Why not use that instead of passing strings?

answered Feb 25, 2014 at 14:13

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ramesh Rajendran · Accepted Answer · 2014-02-25 13:03:05Z

0

Change

refresh_query.Append("date='" +now + "' ");

to

refresh_query.Append("date='" +now.ToString() + "' ");

answered Feb 25, 2014 at 13:03

Ramesh Rajendran

38.9k49 gold badges159 silver badges241 bronze badges

2 Comments

MikkaRin Over a year ago

This approach will not work in case when DB using another culture

dyk Over a year ago

@MikkaRin how do i know what culture is my DB using?

MikkaRin · Accepted Answer · 2014-02-25 14:11:05Z

0

Check how date stores in sql by using

select getdate()

In your code

refresh_query.Append("date='" +now.ToString(_formatExpression_) + "' ");

where formatExpression - is format that corresponds to format of date in your SQL (http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo(v=vs.110).aspx#properties)

answered Feb 25, 2014 at 14:11

MikkaRin

3,08422 silver badges35 bronze badges

Collectives™ on Stack Overflow

Insert/Update SQL DateTime From asp.net(C#) System.dateTime

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related