not deleting, SQL

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
spudmccAsked:
Who is Participating?
 
et_meCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
spudmccAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, what about:

delete from dbo.Sales
where [Data Date]=cast('12:11:54' as time) and
WholesaleID='83-SW';
0
 
spudmccAuthor Commented:
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
 
nishant joshiTechnology Development ConsultantCommented:
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
 
spudmccAuthor Commented:
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
 
Ess KayEntrapenuerCommented:
try this


delete from Sales
where [Data Date] like '12:11:54%' and
WholesaleID = '83-SW'
0
 
spudmccAuthor Commented:
got this message.  It was an error message.

SQL.sql: 0 rows affected [0.768s]
0
 
spudmccAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.