需要删除具有特定DateTime的行 [英] Need to delete rows with a specific DateTime

问题描述

我的客户端在SQL Server数据库的表中有一些重复的条目。我试图删除重复的条目，但到目前为止没有成功。在下面的示例中，我已将表的实际名称更改为tableName，但DateTime列的名称为DateTime，其类型为DateTime。注意，我没有创建一个列名与其数据类型相同的数据库，我只需要使用它。我首先尝试过：

I have a client who got some duplicate entries in a table in a SQL Server database. I am trying to delete the duplicate entries, but without success so far. In the examples below I have changed the actual name of the table to tableName, but the name of the DateTime column is "DateTime" and its type is DateTime. Note, I did not create a database with a column name the same as its datatype, I just have to work with it. I first tried:

DELETE FROM tableName WHERE DateTime = '7/3/2014 3:35:26 PM'

我做了一些研究表明我应将DateTime列的名称括在方括号中，并将导致以下SQL的日期转换为：

I have done some research which indicated that I should enclose the name of the DateTime column in square brackets and cast the date which led to the SQL below:

DELETE FROM tableName WHERE [DateTime] = CAST('7/3/2014 3:35:26 PM' AS DateTime)

这些SQL语句在LINQPad4中运行时不会出现任何错误，但它们不会删除ro ws与给定的数据。

关于我做错的任何想法？

These SQL statements do not give any errors when run in LINQPad4, but they don't delete the rows with given data.
Any ideas on what I am doing wrong?

推荐答案

2014年7月3日是3月7日或7月7日。三月？



尝试使用设置日期格式之前[ ^ ]命令 DELETE 语句到临时更改日期格式。

7/3/2014 is 3. July or 7. March?

Try to use SET DATEFORMAT[^] command before DELETE statement to temporary change date format.

SET DATEFORAT mdy;
SELECT CAST('7/3/2014 3:35:26 PM' AS DATETIME) AS MyDate

或

or

SET DATEFORAT dmy;
SELECT CAST('7/3/2014 3:35:26 PM' AS DATETIME) AS MyDate

检查将传递的转换，并将其与 DELETE <一起使用/ code>声明。



但是......你写道，你试图删除重复项。我建议使用类似的语句：



Check what conversion will be passed and use it with DELETE statement.

But... You wrote that you're trying to delete duplicates. I'd suggest to use sql statement similar to:

SELECT FirstField, SecondField, COUNT(FirstField) AS CountOfDuplicatedRecord
FROM TableName
HAVING COUNT(FirstField)>=1
GROUP BY FirstField, SecondField

来检查重复项。



有几种方法可以实现这一点。您无需手动识别记录。有关详细信息，请参阅：

从SQL Server中的表中删除重复行 [ ^ ]

SQL SERVER - 删除重复记录 - 行 [ ^ ]

从SQL Server中的表中删除重复项 [ ^ ]

to check for duplcates.

There are several ways to achieve that. You don't need to identify records manually. For further information, please see:
Remove Duplicate Rows from a Table in SQL Server[^]
SQL SERVER – Delete Duplicate Records – Rows[^]
Removing Duplicates from a Table in SQL Server[^]

最喜欢的造成这类问题的原因是表格中的时间不是 <$ em> c> 15:35:26 ，但也包括毫秒。用 SELECT 替换 DELETE 将显示是否有任何行符合条件。



在查询中使用明确的日期格式也是一个好主意，以确保 DATEFORMAT 设置不会影响日期的方式解释。最好使用的格式是 yyyyMMdd ，没有任何分隔符。



要删除与之匹配的行第二，忽略毫秒，你需要使用日期范围：

The most likely cause of this sort of problem is that the time in the table is not precisely 15:35:26, but includes milliseconds as well. Replacing the DELETE with a SELECT will show you whether any rows match the condition.

It's also a good idea to use an unambiguous date format in your queries, to ensure that the DATEFORMAT setting doesn't affect how the date is interpreted. The best format to use is yyyyMMdd, without any separators.

To delete the rows which match up to the second, ignoring milliseconds, you'll need to use a date range:

DELETE 
FROM tableName 
WHERE [DateTime] >= '20140703 15:35:26'
And [DateTime] < '20140703 15:35:27'

这篇关于需要删除具有特定DateTime的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！