Need to delete rows with a specific DateTime

Question

1.00/5 (1 vote)

See more:

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:

SQL

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

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:

SQL

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

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?

Posted 7-Jul-14 9:09am

BillW33

Add a Solution

Comments

Sergey Alexandrovich Kryukov 7-Jul-14 15:17pm

"7/3" can be interpreted as day/month or month/day. You should also provide the format for parsing this time string.
—SA

Richard Deeming 7-Jul-14 15:54pm

Is the datetime in the table *precisely* 15:35:26, or could there be milliseconds in there as well?

What do you get if you replace "DELETE" with "SELECT"?

And, as Sergey said, you should use an unambiguous date format: '20140703 15:35:26'

BillW33 7-Jul-14 16:25pm

The problem was the time not being precisely 3:35:26 PM. Yes, the unambiguous date format is better, but I got it working even without that. If you put your comment in the form of a solution I will mark it as the answer.
I found that the following did what I needed:
DELETE FROM FtirSpectrum WHERE DateTime >= '2014-07-03T15:35:26' AND DateTime < '2014-07-03T15:35:27'
Just for fun I tried the following query which worked despite not having unambiguous datetime formats:
select *
from tableName
where DateTime >= '2014-07-03T15:35:26' AND DateTime < '2014-07-03T15:35:27'

Kornfeld Eliyahu Peter 7-Jul-14 15:55pm

1. You should not use column names that same like one of the keywords/functions/type names and so, used by SQL. If you do sou you must enclose them in square brackets ([]) to tell SQL to override the original meaning here...
2. Try using CONVERT with the proper datetime code instead of CAST or use SET DATEFORMAT before running the delete query...

BillW33 7-Jul-14 16:30pm

1. I agree. As I said I did not create the table. Yes, enclosing in square brackets is the recommended fix, but I got it to work in LINQPad both with and without using the square brackets.
2. I tried CONVERT, but it didn't help. The problem was, as pointed out by Richard Deeming, that the times did not precisely match.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-07-07T10:03:00

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

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

SQL

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

or

SQL

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

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:

SQL

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

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[^]

Richard Deeming · Accepted Answer · 2014-07-07T11:28:00

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:

SQL

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

Need to delete rows with a specific DateTime

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0