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()"

score 2 · Answer 1 · answered Jul 08 '11 at 21:29

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 08 '11 at 21:29

Tomas Voracek

5,886
1
25
41

That would be convenient. I'll check into it. – elbillaf Jul 08 '11 at 21:34
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. – Tomas Voracek Jul 08 '11 at 21:46
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. – elbillaf Jul 08 '11 at 21:51

score 1 · Answer 2 · answered Jul 08 '11 at 21:28

1

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

answered Jul 08 '11 at 21:28

NoAlias

9,218
2
27
46

score 1 · Accepted Answer · answered Jul 08 '11 at 21:28

1

How about...

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

answered Jul 08 '11 at 21:28

Michael Fredrickson

36,839
5
92
109

That worked! I can't mark this as the correct answer for a few more minutes, but thanks! – elbillaf Jul 08 '11 at 21:33
-1 Setting date of insertion should be handled by DB, not application. – Tomas Voracek Jul 08 '11 at 21:33
@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. – Michael Fredrickson Jul 08 '11 at 21:45

score 1 · Answer 4 · answered Jul 09 '11 at 08:00

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.

score 0 · Answer 5 · answered Mar 09 '12 at 14:30

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 09 '12 at 14:30

Joel Coehoorn

399,467
113
570
794

SQLServer INSERT getdate() function using parameters

5 Answers5