Cannot update DateTime field in MSSQL Server

Question

I have an update clause in my ASP.NET MVC project and I just want to update single field using current time. I know I could also use GETDATE(), but I need to pass the date parameter from Service layer. So, the query is exactly as shown below and I also tried to use it on MSSQL Server, but encounter the same error: "Incorrect syntax near '.2019'."

UPDATE com.[Ticket] SET Updated = 29.04.2019 15:25:58 WHERE [Id] = 103

Here is the code side where I build the sql clause:

DateTime lastUpdate = DateTime.Now;
string sql = "UPDATE com.[Ticket] SET Updated = " + lastUpdate + " WHERE [Id] = " + model.Id;

Updated field is n datetime type. Do I have to format the date parameter in the update clause?

You're apparently substituting in text values in a query. Don't do that. Read up on how to use properly typed parameters. You avoid this issue, and as a significant bonus you also avoid SQL injection. — Jeroen Mostert, Apr 29 '19 at 12:38
if passing values somehow, use variable typed, so it is typed properly when put into the query. — Brad, Apr 29 '19 at 12:39
`'2019-04-29 15:25:58'` and since you are using a query just pass a datetime param — Ilyes, Apr 29 '19 at 12:39
Use `SqlCommand` and its `Parameters` property, or something like [Dapper](https://github.com/StackExchange/Dapper/blob/master/Readme.md) if you don't want to write boilerplate code. In general, you don't want to create queries by concatenating text, even if you're passing "safe" constants; it leads to plan cache pollution. — Jeroen Mostert, Apr 29 '19 at 12:42
Not a duplicate but related: https://stackoverflow.com/a/45415304/3094533 And another one: https://stackoverflow.com/a/41868383/3094533 — Zohar Peled, Apr 29 '19 at 13:11

score 2 · Accepted Answer · answered Apr 29 '19 at 12:43

2

Parameterize your query. Then you don't need to worry about the format of the date string and, more importantly, you're not open to SQL injection.

string sql = "UPDATE com.[Ticket] SET Updated = @date WHERE [Id] = @id";
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddWithValue("@date", DateTime.Now);
command.Parameters.AddWithValue("@id", model.Id);

answered Apr 29 '19 at 12:43

Owen Pauling

11,349
20
53
64

4

Avoid [`AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/), supply proper types. – Jeroen Mostert Apr 29 '19 at 12:45
1

Upvoted, but you really should read the link Jeroen provided and stop using `AddWithValue`. – Zohar Peled Apr 29 '19 at 13:12

score -1 · Answer 2 · answered Apr 29 '19 at 12:39

-1

Date can be formatted:

UPDATE com.[Ticket] SET Updated = '2019/04/29 15:25:58' WHERE [Id] = 103

answered Apr 29 '19 at 12:39

VT Chiew

663
5
19

3

This is not one of the [safe formats](https://dba.stackexchange.com/q/166771/97345) for date/time literals and it should not be used, even if you were keen on supplying the query as raw text (which is not appropriate in this case). – Jeroen Mostert Apr 29 '19 at 12:44

Cannot update DateTime field in MSSQL Server

2 Answers2