Help with SQL code.

Databases
1

Hello all,

I am trying to get a SQL command to work using a range for dates. I can get the code to work without the dates, but not with:
Without:

SELECT transactions.unit_id, transactions.fname, transactions.lname, transactions.address, transactions.door_state, transactions.event_code, transactions.sql_date_time FROM transactions WHERE transactions.event_code =1105 Or transactions.event_code =1101 Or transactions.event_code =1102 Or transactions.event_code =1103 Or transactions.event_code =1104 Or transactions.event_code =1107 Or transactions.event_code =102 ;

But when I add a data parameter (either at the end of the code above, or at the WHERE command, nothing is returned.

SELECT transactions.unit_id, transactions.fname, transactions.lname, transactions.address, transactions.door_state, transactions.event_code, transactions.sql_date_time FROM transactions WHERE  ( transactions.sql_date_time >=2013-11-01 AND transactions.sql_date_time <=2013-12-30 ) AND ( transactions.event_code =1105 Or transactions.event_code =1101 Or transactions.event_code =1102 Or transactions.event_code =1103 Or transactions.event_code =1104 Or transactions.event_code =1107 Or transactions.event_code =102 ) ;

Can anyone suggest where my error is? There are many many records that meet the criteria…

Thanks!
Tim

2

Put your dates in single quotes. Checking the database objects error message would probably tell you this.

3

Thank you Wayne.

That worked. But strangely, there was no DB error at all. Just no records returned.

Thank you again!
Tim

4

No there should be no error. This is correct form of SQL but just because it looks like a duck doesn’t mean it’s a duck.

‘2013-11-01’ is a date 2013-11-01 = 2001 (those are minus signs not hyphens)

5

Hi Jym,
I see what you mean! That makes total sense. I specifically used the SQLString form to avoid date type problems. Guess that failed too!

In any case, I appreciate the help/feedback. I was beginning to get a bit crazy!

Thanks all,
Tim

6

Tim, sorry I had never entered a sql date without the quotes so assumed there would be an error message. If I was a bit terse please accept my apology. Glad you solved your problem though.

7

Hi Wayne!

I did not interpret your feedback as anything but helpful!

I had been hoping to see an error, which is actually why I tried to SQL command in another tool to be see if an error would be thrown… but it never had. Having spent some time on various changes to the command to try and get it to work - including at the end just the dates - I could see that there was definitely a problem with my date command structure. But not knowing what it was, I came to the forum. You immediately saw the problem and told me of it which made my life soooo much easier!

In the past, you have provided me and many many others with responses to posts that have helped.

I thank you again, and wish you all a Happy New Year!
Tim

8

Read about ‘parameterized sql’ if your database supports it. This traps more errors and guards against SQL Injection Attacks.

9

Look up Prepared Statements

10

Thanks guys, will have a look at these!
Tim