SQLServer INSERT getdate() function using parameters

Question

What is the preferred method of adding the current date/time into an SQL Server table with INSERT Command and executenonquery? I'm expecting something like ...

        cmd.Parameters.Add("@theDate", SqlDbType.VarChar, 20)
        cmd.Parameters("@theDate").Value = "getdate()"

Tomas Voracek · Accepted Answer · 2011-07-08 21:29:11Z

2

First of all, you should use DateTime data type for column. Second, on that column you can use DefaultValue=getdate() constraint, so it is defined only in DB. Application do not need to insert it.

answered Jul 8, 2011 at 21:29

Tomas Voracek

5,9441 gold badge28 silver badges41 bronze badges

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

3 Comments

elbillaf Over a year ago

That would be convenient. I'll check into it.

Tomas Voracek Over a year ago

Downvoters should explain what is wrong as defined in SO policy. This is perfectly reliable and standard way of doing things, i want to know what is incorrect.

elbillaf Over a year ago

Thomas, I'm not sure what you mean. I didn't downvote your answer. In fact, I upvoted it, because it's informative. MF's answer worked for me immediately. I can't check your answer right now (though I assume it will work), because the only access I have to the database is through the aspx programs I write myself - and the database admin is on vacation. I appreciate your response. thanks, tff.

Michael Fredrickson · Accepted Answer · 2011-07-08 21:28:39Z

1

How about...

cmd.Parameters.Add("@theDate", SqlDbType.DateTime)
cmd.Parameters("@theDate").Value = DateTime.Now

answered Jul 8, 2011 at 21:28

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

3 Comments

elbillaf Over a year ago

That worked! I can't mark this as the correct answer for a few more minutes, but thanks!

Tomas Voracek Over a year ago

-1 Setting date of insertion should be handled by DB, not application.

Michael Fredrickson Over a year ago

@Tomas His question was very specific, and this answers that question. If the date is ever anything besides the current time, then a default value in the database isn't sufficient and he'll need to know how to do this.

NoAlias · Accepted Answer · 2011-07-08 21:28:07Z

1

cmd.Parameters("@theDate").Value = Now()

answered Jul 8, 2011 at 21:28

NoAlias

9,2032 gold badges30 silver badges46 bronze badges

Comments

rkw · Accepted Answer · 2011-07-09 08:00:22Z

1

The date/time parameter is NOT needed.

Anytime the stored procedure is called, you just update the date/time field at the server level.

If you want to be able to update the date/time field to something OTHER than the current time then you can add an optional parameter on the stored procedure; this way you server can update it to getdate() when it is null, or you can pass it from the application when it needs to be a specific time.

The reason Tomas emphasized the importance of setting the time at the server level is because of time zones and other factors.

answered Jul 9, 2011 at 8:00

rkw

7,2974 gold badges28 silver badges39 bronze badges

Comments

Joel Coehoorn · Accepted Answer · 2012-03-09 14:30:10Z

0

I would look to see if you can place the text current_timestamp directly into the sql code, and skip the parameter entirely. If you can't, then it's time to just use DateTime.Now.

answered Mar 9, 2012 at 14:30

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Collectives™ on Stack Overflow

SQLServer INSERT getdate() function using parameters

5 Answers 5

3 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related