在SQL Server中更新时间记录的问题 [英] Problem to update time records in SQL Server

问题描述

我的数据库中有大约6万条记录，我需要SQL语句可以帮助我更新大部分记录而不是全部记录因为当我们输入记录时我们不知道其中一台计算机有不同的时间我们进入早上9点的记录，但不同的电脑是在晚上9点。



正确记录的一个例子是：



''2013-06-01 09：54：31.437''

''2013-05-31 09：55：33.857''

'' 2013-06-01 09：58：49.190''



和错误的记录是：



''2013-05-31 21：55：09.223''

''2013-05-31 21：56：41.337''

''2013-05- 31 21：57：50.553''



谢谢你的帮助

I have around 60 thousand records in my database and I need SQL statement can help me to update most of this records not the all because when we were entering the records we not know that one of the computer have different time we enter the records in the 9 am but the different computer was in 9 pm.

An example of the correct records are:

''2013-06-01 09:54:31.437''
''2013-05-31 09:55:33.857''
''2013-06-01 09:58:49.190''

and the wrong records are :

''2013-05-31 21:55:09.223''
''2013-05-31 21:56:41.337''
''2013-05-31 21:57:50.553''

Thank You For Helping Me

推荐答案

你的问题不是明确。你的清单显示有些电脑在晚上9点有记录，你的意思是时间部分是错误的你要修理它吗？



我认为这样做了：

Your question is not clear. Your list shows that some computers had the records at 9 pm, do you mean that the time portion is wrong and you want to fix it ?

I think this does it:

update blah set col = dateadd(hour, -12, col)
where datepart( hour, col) > 12

它说在表格blah，将名为col的列更新为比当前小12小时（因此，从晚上9点到上午9点，从晚上10点到上午10点，从前一天上午10点到晚上10点），其中小时部分日期大于12（所以我给出的最后一个例子不会发生，只有中午过去的时间，去掉12小时）。如果需要，您可以根据自己的情况稍微改变一下逻辑。

It says that in table blah, update the column called col to be 12 hours less than what it currently is ( so, from 9 pm to 9 am, from 10 pm to 10 am, from 10 am to 10 pm the previous day ), where the hour portion of the date is greater than 12 ( so the last example I gave would not occur, only times past midday, get 12 hours removed ). You can change the logic a little further if you need to, to suit your circumstance.

这篇关于在SQL Server中更新时间记录的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！