SQL statment for comparing date time field

Question

0.00/5 (No votes)

See more:

I am fairly new to C# and am having trouble with this SQL statement. I have scoured google looking for an answer but cannot find anything.

I am reading in a Date/Time field EngageDate from a Access DB and then comparing to another Date/Time field varDate which is entered by the user using the date/time picker on the form.
I am not sure what operator I am missing here... any/all help is appreciated... thanks!

SQL STATMENT:

C#

strSQL = "SELECT * from Engagements WHERE CardNum = @dblCardLookUpHold AND DivisionID = @dblDivFlag  AND EngageDate = "+ varDate +" ";

THIS RETURNS THIS SYNTAX ERROR:

Syntax error (missing operator) in query expression 'CardNum = @dblCardLookUpHold AND DivisionID = @dblDivFlag  AND EngageDate = 1/8/2011 12:00:00 AM'.

Posted 10-Jan-11 6:09am

WhiskeyBusiness

Updated 10-Jan-11 6:24am

Henry Minute

v4

Add a Solution

4 solutions

Solution 1

Are you sure these parameters are being set properly?

@dblCardLookUpHold
@dblDivFlag

Also, my experience with this error is the lack of ' around strings, what is data type is CardNum and DivisionID stored in the table as?

Posted 10-Jan-11 6:14am

wizardzz

Comments

WhiskeyBusiness 10-Jan-11 12:21pm

.Thanks for the quick reply!

I am setting

CardNum and DivisionID are both stored as doubles.

@dblCardLookUpHold@dblDivFlag

cmd.Parameters.Add("dblCardLookUpHold",SqlDbType.Float).Value = dblCardLookUpHold;cmd.Parameters.Add("dblDivFlag", SqlDbType.Float).Value = dblDivFlag;

wizardzz 10-Jan-11 12:24pm

Have you tried putting the quote around your date variable yet?

WhiskeyBusiness 10-Jan-11 12:44pm

I tried and recieve a datatype mismatch.

strSQL = "SELECT * from Engagements WHERE EngageDate = '" + varDate +"'";

Solution 2

use single quote around your date

SQL

strSQL = "SELECT * from Engagements WHERE CardNum = @dblCardLookUpHold AND DivisionID = @dblDivFlag AND EngageDate ='" + varDate + "'"";

Posted 10-Jan-11 6:17am

Yusuf

Comments

WhiskeyBusiness 10-Jan-11 12:29pm

Thanks for the quick reply! I tried your suggestion and recieve an OleDBException.

Data type mismatch in criteria expression.

I dont understand this since they are both date/times being compared....?

Solution 4

Add watch to strSQL variable.Also,use Quick Watch to see the complete query which is formed by appending all values from C#.Execute that query at your backend.This will help you to modify & correct it.

Posted 10-Jan-11 6:34am

Anupama Roy

Updated 10-Jan-11 6:35am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Geoff Williams · Accepted Answer · 2011-01-10T06:35:00

Solution 5

Dates (and times) often give problems in SQL statements. The most robust way of handling them is to pass them as parameters to SQL queries.

Change your query to

strSQL = "SELECT * from Engagements WHERE CardNum = @dblCardLookUpHold AND DivisionID = @dblDivFlag AND EngageDate = @EngageDate";

and add

cmd.Parameters.Add("@EngageDate", SqlDbType.DateTime).Value = EngageDate;

to your code.

You will also need to fix your existing parameter assignement statements - you need to include the @ character as part of the parameter name:

cmd.Parameters.Add("@dblCardLookUpHold", SqlDbType.Float).Value = dblCardLookUpHold;
cmd.Parameters.Add("@dblDivFlag", SqlDbType.Float).Value = dblDivFlag;

Posted 10-Jan-11 6:35am

Geoff Williams

Updated 10-Jan-11 6:36am

v2

Comments

WhiskeyBusiness 10-Jan-11 12:50pm

Thanks! I tried this but the statement will not return the record.

I can hardcode what I am looking for and the value gets returned.
strSQL = "SELECT * from Engagements WHERE EngageDate LIKE '12/15%'";

However, when I try your suggestion the record is not found..?

cmd.Parameters.Add("@varDate", SqlDbType.DateTime).Value = varDate;

strSQL = "SELECT * from Engagements WHERE EngageDate LIKE @varDate";

Geoff Williams 10-Jan-11 13:14pm

If you are looking to use only the date values of your EngageDate field then you could change the last part of your query from

AND EngageDate = @EngageDate

to

AND YEAR(EngageDate) = YEAR(@EngageDate) AND MONTH(EngageDate) = MONTH(@EngageDate) AND DAY(EngageDate) = DAY(@EngageDate)

WhiskeyBusiness 10-Jan-11 13:15pm

Thanks! Your suggestion put me on the right track... this below worked.

cmd.Parameters.AddWithValue("@varDate", SqlDbType.DateTime).Value = varDate;