CODEWITHSUNDEEP
c#sql-serverdate
Alfredo Torre
Alfredo Torre

Reputation: 728

C# SQL Server compare date with string

I have to compare a table field that is a date stored as varchar in the format 'dd/MM/yyyy' with a date value, but the comparison fails. I have exception

Conversion failed when converting date and/or time from character string. I tried converting the date to compare i nstring, like this 

string dateFormat = date.ToString("dd/MM/yyyy");

and then write the query like this:

string sql = "select * from TB_RICHIESTE where CONVERT(DATE, Date) <= CONVERT(DATE, '" + dateFormat + "')";

But I have this excpetion. Someone can help me? Thanks

Upvotes: 1

Views: 4669

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

First, you should not store dates as strings.
As Panagiotis Kanavos wrote in his comment - this is a serious bug. You can't sort by such a column, you can't search for date ranges, and most important - you can't control if someone enters an invalid value - nothing is stopping someone from entering "Alfredo" to that column.
For more information, read Aaron Bertrand's Bad habits to kick : choosing the wrong data type.

Second, you should not pass dates from .Net to Sql server as strings. you should pass instances of DateTime as parameters. The .Net DateTime maps directly to SQL Server's Date.

If you can't change the data type of the column, you can at least convert it to date using the proper convert style (103 in your case).

Here is a better way to do it:

var sql = "select * from TB_RICHIESTE where CONVERT(DATE, [Date], 103) <= @Date";

Then you add the @Date parameter to the SqlCommand:

com.Parameters.Add("@Date", SqlDbType.Date).Value = date.Date;

Upvotes: 5

Sandeep
Sandeep

Reputation: 423

Use Parameter to pass date values refer @Zohar Peled post. This is the proper method handling date values. OR You can pass the date value in ISO format, refer the below code.

string dateFormat = date.ToString("yyyy/MM/dd");


string sql = "select * from TB_RICHIESTE where CONVERT(DATE, Date) <= CONVERT(DATE, '" + dateFormat + "')";

Upvotes: 0

Related Questions