CODEWITHSUNDEEP
asp.netsql-server-2008
Sidra Kanwal
Sidra Kanwal

Reputation: 115

Comparision of Two Dates

I want to compare two different dates one current date and other stored in database.

Current Date

DateTime CurrentDate = DateTime.Now;
String Date = CurrentDate.ToString("dd/MM/yyyy");

Query

String query_str = "SELECT * FROM [" + TAdvert_table + "] WHERE ([Country]='China' AND [Viewed] IS NULL) AND ([DateStart]="+Date+")";

Exception: 

{"Operand type clash: date is incompatible with int"}

Table Definition

Id          int Checked
Title           varchar(50) Checked
Description varchar(MAX)    Checked
ImageLocation   varchar(50) Checked
Country         varchar(50) Checked
City            varchar(50) Checked
NrInQueue   varchar(50) Checked
DateStart   date            Checked
DateEnd         date            Checked
NoOfClicks  int         Checked
Paid            varchar(50) Checked
Viewed          varchar(5)  Checked

Upvotes: 0

Views: 67

Answers (2)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

The problem:

After executing following source code:

DateTime CurrentDate = DateTime.Now;
String Date = CurrentDate.ToString("dd/MM/yyyy");
String query_str = "SELECT * FROM [" + TAdvert_table + "] WHERE ([Country]='China' AND [Viewed] IS NULL) AND ([DateStart]="+Date+")";

the query variable could contains this T-SQL query:

SELECT * FROM YourTable WHERE ([Country]='China' AND [Viewed] IS NULL) AND ([DateStart]=11/05/2014)

Following expression: 11/05/2014 is considered as mathematical expression and is evaluated to 0. In SQL Server Management Studio try:

SELECT 11/05/2014 -- > 0

Also the result of this expression 11/05/2014 is an INT (not an DECIMAL/NUMERIC/FLOAT/REAL).

Try:

SELECT SQL_VARIANT_PROPERTY(11/05/2014, 'BaseType') -- > INT

This is the reason why this predicate ([DateStart]=11/05/2014) generates that error Operand type clash: date is incompatible with int. Also, you could try:

SELECT CASE WHEN CONVERT(DATE, GETDATE()) = 11/05/2014 THEN 1 ELSE 0 END

which generates the same error.

Solutions:

1) You could use single quotes (... '"+Date+"'):

String query_str = "SELECT * FROM [" + TAdvert_table + "] WHERE ([Country]='China' AND [Viewed] IS NULL) AND ([DateStart]='"+Date+"')";

Thus, the generated query could be: SELECT ... ([DateStart]='11/05/2014'). This could work or not depending on current DATEFORMAT setting.

Note: I would not use this solution.

2) To avoid the problem caused by different DATEFORMAT settings you could use the following ISO 8601 format YYYY-MM-DD: 

String Date = CurrentDate.ToString("yyyy-MM-dd");
String query_str = "SELECT ... AND ([DateStart]='"+Date+"')";

Note: I would not use this solution.

3) A better approach is to use parameterized queries: 

SqlCommand command = new SqlCommand("SELECT ... WHERE ([Country]='China' AND [Viewed] IS NULL) AND ([DateStart]=@pDateStart)", connection);
command.Parameters.Add("@pDateStart", SqlDbType.Date);
command.Parameters["@pDateStart"].Value = CurrentDate;

Note: generating SQL queries by concatenating strings (for example: table name) can be dangerous.

Upvotes: 3

qxg
qxg

Reputation: 7036

Change end of the query to 

"([DateStart]='"+Date+"')"

Upvotes: 0

Related Questions