SQL Statement not working - any advice?

[dommy]

Hi,

I am trying to grab information from 2 tables with the same fields where the newsletter option has been selected. This isn't casuing me a problem. However what is, is when I introduce a query for the date i.e. show info where the dateadded is after x, it always returns all the information regardless of the date I specify.. what am I doing wrong?

Here's my code:


tDate = "10/10/2010"

set RS1 = objConn.Execute ("SELECT FirstName, SurName, Email, DateAdded FROM Crew WHERE Newsletter = True AND DateAdded >" & tDate & " UNION SELECT FirstName, SurName, Email, DateAdded FROM Clients WHERE Newsletter = True AND DateAdded >" & tDate )


So when reading back the results, theres data in there all the way back to January 2010??

[Old Pedant]

It would probably help if you learned to DEBUG.


tDate = "10/10/2010"

SQL = "SELECT FirstName, SurName, Email, DateAdded " _
& " FROM Crew WHERE Newsletter = True AND DateAdded >" & tDate _
& " UNION " _
& " SELECT FirstName, SurName, Email, DateAdded " _
& " FROM Clients WHERE Newsletter = True AND DateAdded >" & tDate
Response.Write "DEBUG SQL: " & SQL & "<HR>"

set RS1 = objConn.Execute ( SQL )
...

Okay, so what did that show you ?

Something like (part of the query)
... WHERE DateAdded > 10/10/2010 ...

Right?

Okay, so what do you think the expression 10/10/2010 *IS*???

HINT: IT IS NOT A DATE!

It's simply arithmetic. 10 divided by 10 and then divided by 2010.

So you are REALLY asking for
... WHERE DateAdded > 0.00049 ...
(approximately). And that "date" is roughly 1/1/1900, depending on the DB you are using.

To specify a date in SQL, you *MUST* (a) format it the way the database understands and (b) put the proper delimiters around it.

And the way to do that depends *HEAVILY* on the the database you are using.

SO:

Access:
... WHERE DateAdded > #10/13/2010# ...
OR
... WHERE DateAdded > #2010/10/13# ...

SQL Server:
... WHERE DateAdded > '10/13/2010' ...

MySQL:
... WHERE DateAdded > '2010-10-13' ...

I purposely changed your date to the 13th so you can tell which is the month and which is the day.

And I think Oracle has yet another format required. Note that for MySQL you must use YYYY-MM-DD. Access allows two forms.

SQL Server actually allows '10/13/2010' or '13/10/2010' or '2010/10/13' depending on the locale that it was installed for. Or you can specify which you want to use with a "SET" command: SET DATEFORMAT MDY or DMY or YMD, where the meaning should be obvious.

But not that all of them require delimiters. Access, alone among DBs, requires #...#.

[dommy]

Hi Old Pedant,

I do debug, here's why I was lost..

I probably wasted hours yesterday trying to rattle my brain on this one.. because I'm still not sure the date format was the issue.. I say that because I also tried this:



tDate = Date()



..which I was expecting to insert the correct date format/expression for the statement. Knowing that I had no records that were as new as the current date, I was expecting to get back 0 records, instead it showed them all! So on reflection, perhaps I still needed # delimiters in this case too?

I appreciate what you are saying about the expression 10/10/2010 being a math sum, but again I wouldn't have automatically guessed this because I would expect a math sum to ALSO be enclosed in delimiters in order to work!

Note: I was using 10/10 as a starting base because I have to always consider whether the date format is U.S. or UK on the server and I was in this case only interested in the month. An issue I'm always up against, why there can't be one date format I don't know, but some folks just have to be different - PAL/NTSC being another! And before anyone mentions the web.config, I am well aware of this and use it where I can.

Funny thing is, I had come across the # # delimiter and dates only a while back, with a similar issue, so I MUST remember that one and I think with your helpful explanation, It's drilled home!

Thanks Pendant! ;)

Here's my final code, fixing the the date to U.S. format as the system seems to be in this format.


tDate = Request.Form("ddate")
myDate = CDate(tDate)
setDate = month(myDate) & "/" & day(myDate) & "/" & year(myDate)

' Testing to see date format.
'response.write setDate

set RS1 = objConn.Execute ("SELECT FirstName, SurName, Email, DateAdded FROM Crew WHERE Newsletter = True AND DateAdded > #" & setDate & "# UNION SELECT FirstName, SurName, Email, DateAdded FROM Clients WHERE Newsletter = True AND DateAdded >#" & setDate & "#" )

[Old Pedant]

Since you are using #...#, that means the DB you are using must be Access (or at least you are using the JET driver...same thing, really).

And that being so, then indeed you MUST use either MM/DD/YYYY format or YYYY/MM/DD format. (The delimiter isn't important...could be YYYY-MM-DD or even YYYY.MM.DD).

That's because Access is slightly brain-dead. Sorry.

The funny thing about Access is that if you used a date such as #13/10/2010# *THEN* it would work!!! When the first number is greater than 12 then and only then Access figures out that it must be the day of the month and automatically changes gears. (Well, to be fair, this is not Access per se doing it. It's the underlying COM functions, that were written back in the early 1990s and can't be changed today or all sorts of code would break.)

Personally, I recommend going with YYYY-MM-DD. It means that if you ever opt to change to MySQL you will be ready to go (as that's the only form MySQL understands) and you *CAN* use that format with SQL Server, as well. That is, it's the only universal form.

You can do it thus:

CONST DATEDELIMITER = "#"
Function YMD( dt )
If IsDate(dt) Then
YMD = DATEDELIMITER & Year(dt) & "-" & Month(dt) & "-" & Day(dt) & DATEDELIMITER
Else
YMD = "NULL"
End If
End Function

...
setDate = YMD(myDate)
SQL = "SELECT FirstName, SurName, Email, DateAdded " _
& " FROM Crew WHERE Newsletter = True AND DateAdded > " & setDate _
& " UNION " _
& " SELECT FirstName, SurName, Email, DateAdded " _
& " FROM Clients WHERE Newsletter = True AND DateAdded > " & setDate
Response.Write "DEBUG SQL: " & SQL & "<HR>"

set RS1 = objConn.Execute ( SQL )
...

And then, if you change to SQL Server or MySQL, you only need to change
CONST DATEDELIMITER = "'"

[dommy]

Actually, I've used yyyy/mm/dd on other parts of the site, so should follow that rule - thank you for the detailed explanation and example. I learnt ASP/Access when I started out, and it is unfortunately a dying breed (however that said there's very little I haven't been able to do with it from all my requests) ..

I am considering migrating to php / mysql. Any thoughts on this decision? I was contemplating asp.net but am worried it will introduce limitations server wise etc.

Many thanks again ;)

[Old Pedant]

It really depends on what kind of sites you will produce.

Truly complex sites are probably best built with ASP.NET. The learning curve is pretty steep, but once you climb it you begin to realize how rich the environment is.

But let's face it, ASP.NET is way overkill for many, many sites. PHP is a reasonable replacement for ASP coding. Most of the concepts are really the same, just new syntax and a new library to learn.

Yes, I agree. ASP, for all it is getting old an creaky, is still usually he easiest way to toss together a quick site. At least for me.

[dommy]

;) Thanks