NOT IN operator in sp for date field

Question

How to use NOT IN operator in sp for a date field. I need to delete some dates through a delete query inside sp. DELETE FROM table1WHERE [Date] NOT IN (@someDates) @someDates is a parameter which holds date like this '05-29-2017'  '07-04-2017'  '09-04-2017' It is giving error as follows Conversion failed when converting date and/or time from character string Please suggest

Rafnas T P · Answer

when you send dates to sp convert the dates to datetime so that it convert to proper date format for this you can use Convert.ToDateTime( '05-29-2017')Convert.ToDateTime( '07-04-2017') etc

Manas Mohapatra · Answer

As per @someDates value it should work perfectly. Please cross-verify that it should not any extra chars or anything. Execute below sql query in your SP and see if it works. If yes then please check value @someDates variable. SELECT * FROM [dbo].[Tags] WHERE CreatedDate in ( '05-29-2017'  '07-04-2017'  '09-04-2017' )

Ramesh Barik · Answer

This issue was occoured due to invalid format of datetime. So format your date parametr. Ex: select * FROM table1 WHERE [Date] NOT in (convert(datetime'15-03-2012'103)convert(datetime'15-03-2012'103)). Try this.