How to Insert System.Datetime object into SqlServer via DbCommand with parameters

Question

I try to do this for couple of hours.

            Database db = DBUtil.GetInstance().GetDataBase();
            DbCommand cmd = db.GetSqlStringCommand(@"INSERT INTO [XXX] (
            ...
                                                                  ,[FirstDate]
            ...
            ) VALUES ('@FirstDate',...");

            db.AddInParameter(cmd, "@FirstDate", DbType.Date, DateTime.Now );

But I always get this exception information below.

{System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

what should I do for the conversion ? Or pick up another DbType ? DbType.DateTime2 ?

I generally prefer to use DateTime2. It's more flexible and is a recommended practice — Mathieu
– Mathieu, Commented Mar 24, 2012 at 15:48

Min Min · Accepted Answer · 2012-03-24 15:51:51Z

2

You don't need to add quote for parameter in Insert Statement. (Values('@FirstDate'))

    Database db = DBUtil.GetInstance().GetDataBase();
    DbCommand cmd = db.GetSqlStringCommand(@"INSERT INTO [XXX] (
    ...
                                                          ,[FirstDate]
    ...
    ) VALUES (@FirstDate,...");

   db.AddInParameter(cmd, "@FirstDate", DbType.DateTime, DateTime.Now );

answered Mar 24, 2012 at 15:51

Min Min

6,2282 gold badges21 silver badges17 bronze badges

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

Comments

Brad Rem · Accepted Answer · 2012-03-24 15:49:30Z

1

Depends on what the type is in your database if it's Date or DateTime.

// full date and time
db.AddInParameter(cmd, "@FirstDate", DbType.DateTime, DateTime.Now );

or

// just the date portion
db.AddInParameter(cmd, "@FirstDate", DbType.Date, DateTime.Now.Date );

answered Mar 24, 2012 at 15:49

Brad Rem

6,0262 gold badges27 silver badges50 bronze badges

2 Comments

Jon Skeet Over a year ago

That's not the problem here - see minmin's answer.

Brad Rem Over a year ago

@JonSkeet, ah, yes, I see that now...but at least give me half credit for getting the other problem! :-)

Collectives™ on Stack Overflow

How to Insert System.Datetime object into SqlServer via DbCommand with parameters

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related