Q:

Error While Comparing Dates

I have an NVARCHAR column named Receivingdate. I want to compare its content with the current time, but when I call WHERE (Receivingdate < Getdate()), I get the following error:

The conversion of nvarchar to datetime resulted in an out of range value

I insert the data using the following call from C#:
DateTime.Now.AddDays(ces).ToString("dd/MM/yyyy");

I'm wondering what I'm doing wrong.

A:

What you're doing wrong is storing dates in an NVARCHAR column. Please stop doing that. Fix your table and make that column using the DATE data type. The error is caused by garbage data getting into your table. Find those rows using:
SELECT Receivingdate FROM dbo.YourTable 
  WHERE ISDATE(ReceivingDate) = 0;

Now, you'll need to either correct or get rid of those before you fix the data type. Then:
ALTER TABLE dbo.YourTable 
  ALTER COLUMN ReceivingDate DATE;

Next, stop converting to a regional string format when inserting. Just insert the DateTime value from C# without calling .ToString() at all. There is absolutely no reason you should ever be converting to date to string and back again through any of this process, except at the point where you want to display it.

