blocks|key|996651|text|通常情况下，它应该是有效的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|996652|但是你能试试这个吗？我家里的电脑上没有SQL，我不能自己尝试|996653|UPDATE+table
SET+EndDate+=+'2009-05-25+00:00:00.000'
WHERE+Id+=+1|code-block|syntax|javascript|996654|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Normally, it should work.

But can you try this? I don't have SQL on my home PC, I can't try myself

<pre><code>UPDATE table
SET EndDate = '2009-05-25 00:00:00.000'
WHERE Id = 1
</code></pre>

blocks|key|1210962|text|是不是桌子上有个扳机把它放回去了？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1210963|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Is there maybe a trigger on the table setting it back?

blocks|key|996703|text|这应该行得通，我会把Date放在括号里，因为它是一个保留的关键字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|996704|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

That should work, I'd put brackets around [Date] as it's a reserved keyword.

blocks|key|996740|text|字符串文字将根据当前日期格式设置进行传递，请参见SET+DATEFORMAT。一种总是有效的格式是'20090525‘格式。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|996741|当然，现在你需要定义“不工作”。是否未更新任何记录？也许Id=1和任何记录都不匹配...|996742|如果它显示‘一条记录已更改’，那么您可能需要向我们展示您如何验证...|996743|entityMap|0|LINK|mutability|MUTABLE|url|http://technet.microsoft.com/en-us/library/ms189491.aspx^0|O|E|O|E|0|0|S|4|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]]|D|@$9|U|A|V|1|W]]|E|$]]|$1|F|3|G|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|10|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

The string literal is pased according to the current dateformat setting, see <a href="http://technet.microsoft.com/en-us/library/ms189491.aspx" rel="noreferrer"><code>SET DATEFORMAT</code></a>. One format which will always work is the '20090525' one.

Now, of course, you need to define 'does not work'. No records gets updated? Perhaps the <code>Id=1</code> doesn't match any record...

If it says 'One record changed' then perhaps you need to show us how you verify...

blocks|key|1211086|text|当有疑问时，be+explicit+about+the+data+type+conversion+using+CAST/CONVERT|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1211087|UPDATE+TABLE
+++SET+EndDate+=+CAST('2009-05-25'+AS+DATETIME)
+WHERE+Id+=+1|code-block|syntax|javascript|1211088|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms187928.aspx^0|6|1P|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

When in doubt, <a href="http://msdn.microsoft.com/en-us/library/ms187928.aspx" rel="noreferrer">be explicit about the data type conversion using CAST/CONVERT</a>:

<pre><code>UPDATE TABLE
 SET EndDate = CAST('2009-05-25' AS DATETIME)
 WHERE Id = 1
</code></pre>

blocks|key|996829|text|使用DateTime参数是最好的方法。但是，如果您仍然希望将DateTime作为字符串传递，那么在使用语言不可知的格式的情况下，应该不需要强制转换。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|996830|例如：|996831|给定一个如下所示的表：|996832|create+table+t1+(id+int,+EndDate+DATETIME)
insert+t1+(id,+EndDate)+values+(1,+GETDATE())|code-block|syntax|javascript|996833|以下内容应始终有效：|996834|update+t1+set+EndDate+=+'20100525'+where+id+=+1+--+YYYYMMDD+is+language+agnostic|996835|下面的方法将会起作用：|996836|SET+LANGUAGE+us_english
update+t1+set+EndDate+=+'2010-05-25'+where+id+=+1|996837|但是，这不会：|996838|SET+LANGUAGE+british
update+t1+set+EndDate+=+'2010-05-25'+where+id+=+1++|996839|这是因为'YYYY-MM-DD‘不是一种语言不可知的格式(从SQL服务器的角度来看)。|996840|ISO+'YYYY-MM-DDThh:mm:ss‘格式也是语言不可知的，在需要传递非零时间时非常有用。|996841|更多信息：http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes|offset|length|996842|entityMap|0|LINK|mutability|MUTABLE|url|http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes^0|0|0|0|0|0|0|0|0|0|0|0|0|5|1R|0|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1D|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1E|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|1F|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|1H|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|1J|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|1K|8|@]|9|@]|A|$]]|$1|U|3|V|5|H|7|1L|8|@]|9|@]|A|$I|J]]|$1|W|3|X|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1N|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1O|8|@]|9|@$12|1P|13|1Q|1|1R]]|A|$]]|$1|14|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B]]]]

Using a DateTime parameter is the best way.
However, if you still want to pass a DateTime as a string, then the CAST should not be necessary provided that a language agnostic format is used.

e.g. 

Given a table created like :

<pre><code>create table t1 (id int, EndDate DATETIME)
insert t1 (id, EndDate) values (1, GETDATE())
</code></pre>

The following should always work :

<pre><code>update t1 set EndDate = '20100525' where id = 1 -- YYYYMMDD is language agnostic
</code></pre>

The following will work : 

<pre><code>SET LANGUAGE us_english
update t1 set EndDate = '2010-05-25' where id = 1
</code></pre>

However, this won't :

<pre><code>SET LANGUAGE british
update t1 set EndDate = '2010-05-25' where id = 1 
</code></pre>

This is because 'YYYY-MM-DD' is not a language agnostic format (from SQL server's point of view) .

The ISO 'YYYY-MM-DDThh:mm:ss' format is also language agnostic, and useful when you need to pass a non-zero time.

More info : <a href="http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes" rel="nofollow noreferrer">http://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes</a>

blocks|key|996848|text|UPDATE+TABLE
+++SET+EndDate+=+CAST('2017-12-31'+AS+DATE)
+WHERE+Id+=+'123'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|996849|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>UPDATE TABLE
 SET EndDate = CAST('2017-12-31' AS DATE)
 WHERE Id = '123'
</code></pre>

The following query does not update the datetime field:

<pre><code>update table
SET EndDate = '2009-05-25'
WHERE Id = 1
</code></pre>

I also tried it with no dashes, but that does not work either.

How do you update a DateTime field in T-SQL?

服务器

以下查询不会更新datetime字段：update tableSET EndDate = '2009-05-25'WHERE Id = 1我也试过了，没有破折号，但也不起作用。

问如何在T-SQL中更新DateTime字段？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在T-SQL中更新DateTime字段？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在T-SQL中更新DateTime字段？
EN