?
Solved

not deleting, SQL

Posted on 2012-08-21
10
Medium Priority
?
383 Views
Last Modified: 2012-08-22
Hi Experts

I am having trouble with a delete statement that I can't get to work.  I am new to SQL and have very limited knowledge so please be kind.  

The SQL query will work on a backup table but I can't get it to work on the main table.  It seems to work because it doesn't come back with error messages but it says that -0- records have been affected.  The two tables are identical as far as I can see (looking at the columns and definitions).  

Here is the code:

delete from dbo.Sales
where [Data Date]='12:11:54.0000000' and
WholesaleID='83-SW';

I've taken the .0000000 off the time and tried that but that also didn't work.  

Any thoughts or help would be greatly appreciated.

A
0
Comment
Question by:spudmcc
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38317858
what data type is the field [data date] ? I presume datetime, in which case the "format" you put is not really adequate.

please show real data values stored, including date part.
0
 

Author Comment

by:spudmcc
ID: 38317881
it is a (time(7), null) not part of a datetime.  The upload data is in this format:  12:11:54 without all of those zeros.  

If I ask SQL to give me the top 50 lines it comes back with the 12:11:54.0000000  with a period and 7 zeros after the time.  

A
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38317905
well, what about:

delete from dbo.Sales
where [Data Date]=cast('12:11:54' as time) and
WholesaleID='83-SW';
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:spudmcc
ID: 38317948
Msg 402, Level 16, State 1, Line 1
The data types date and time are incompatible in the equal to operator.

I have no idea what this means.
0
 
LVL 14

Expert Comment

by:nishant joshi
ID: 38317957
please let me know are able to select using this condition and how many records are you getting in backup and on main table

SELECT * from dbo.Sales
where [Data Date]=cast('12:11:54' as time) and
WholesaleID='83-SW'

Open in new window

0
 

Author Comment

by:spudmcc
ID: 38317980
I get the same error message on both tables.  Not getting any records at all.

Msg 402, Level 16, State 1, Line 1
The data types date and time are incompatible in the equal to operator.
0
 
LVL 15

Expert Comment

by:Ess Kay
ID: 38318167
try this


delete from Sales
where [Data Date] like '12:11:54%' and
WholesaleID = '83-SW'
0
 

Author Comment

by:spudmcc
ID: 38318494
got this message.  It was an error message.

SQL.sql: 0 rows affected [0.768s]
0
 
LVL 3

Accepted Solution

by:
et_me earned 2000 total points
ID: 38319021
Please try the below:

delete from dbo.Sales
where cast([Data Date] as time)='12:11:54' and
WholesaleID='83-SW';

Open in new window

0
 

Author Closing Comment

by:spudmcc
ID: 38320140
Thank you so much for your help in resolving this issue.  It worked like a champ on both our backup table as well as the primary.  

A
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question