Loading

NOT IN operator in sp for date field

Ajay Mavatkar

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P9y ago
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
0
Manas Mohapatra9y ago
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')
0
Ramesh Barik9y ago
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.
0