Query past events of 2007

[xoozlez]

Hi there,

I have a registration form where I like to filter out the past events of 2007. This is the code I am using :

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND Events.Category LIKE '3' OR Events.Category LIKE '4' OR Events.Category LIKE '5' OR Events.Category LIKE '6' OR Events.Category LIKE '7' OR Events.Category LIKE '8' OR Events.Category LIKE '9' OR Events.Category LIKE '10' OR Events.Category LIKE '11' OR Events.Category LIKE '12' OR Events.Category LIKE '13' OR Events.Category LIKE '14' OR Events.Category LIKE '15' OR Events.Category LIKE '16' OR Events.Category LIKE '17' OR Events.Category LIKE '18' OR Events.Category LIKE '19' OR Events.Category LIKE '20' OR Events.Category LIKE '21' ORDER BY EventName"

The query I used is "EventDateBegin >'01/01/2008'" but it is not working at all.

The code I am using in the drop-down menu:
<option value="<%=rs("EventName")%> - <%=rs("EventDateBegin")%>"><%=rs("EventName")%> - Date: <%=rs("EventDateBegin")%>

Can anybody advice if I am doing the right way?

[jhardman]

The date should be enclosed in pound signs:

EventDateBegin >#01/01/2008#

Let me know if this helps.

Jared

[Nicodemas]

It really depends on the type of database you are using.

MS Access uses octothorpe's (#) to delimit dates; MS SQL Server uses apostrophes. Also, date format is also important.

What isn't working, exactly? Are events from 2007 still showing up,or is the query failing all together?

[xoozlez]

It really depends on the type of database you are using.

MS Access uses octothorpe's (#) to delimit dates; MS SQL Server uses apostrophes. Also, date format is also important.

What isn't working, exactly? Are events from 2007 still showing up,or is the query failing all together?

Hi guys, thank you for getting back to me.

I am using MSSQL, thus apostrophes is using here. It is not working such that all events from 2007 are still showing up. I would think it is something to do with date format, but I have no idea how to proceed on and resolve from there.

[xoozlez]

Hi there, any help? :)

[jhardman]

Hi there, any help? :)

I could have sworn that the octothorpes (that's the first time I've used that word) were the SQL convention for designating dates. It sounds like Nicodemas uses them more than I so he is likely right, but I am still going to suggest it. The apostrophes are definitely used to designate strings in SQL, and I'm surprised (but not astounded) to hear that they would be used for other data types. Let me know if you decide to try it.

Jared

[xoozlez]

I could have sworn that the octothorpes (that's the first time I've used that word) were the SQL convention for designating dates. It sounds like Nicodemas uses them more than I so he is likely right, but I am still going to suggest it. The apostrophes are definitely used to designate strings in SQL, and I'm surprised (but not astounded) to hear that they would be used for other data types. Let me know if you decide to try it.

Jared

Hi Jared,

Thank you for helping me with this problem. I had tried and this is the error I am getting:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '#'.
/Form/index.asp, line 38

:)

[jhardman]

Hi Jared,

Thank you for helping me with this problem. I had tried and this is the error I am getting:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '#'.
/Form/index.asp, line 38

:)

but with ' ' you get get no error, and the where clause is basically ignored? Give me a few minutes, I have a db with a date field in SQL server. I'm just sorting by date, but I can put up a where clause test in 5 minutes.

Jared

[jhardman]

but with ' ' you get get no error, and the where clause is basically ignored? Give me a few minutes, I have a db with a date field in SQL server. I'm just sorting by date, but I can put up a where clause test in 5 minutes.

Jared

OK, Nicodemas is definitely right. I guess I should learn to trust him. I got it to work just fine. I don't use parentheses in my query, but I would be surprised if that affects anything. Here is my query:

SELECT * FROM [works] WHERE [date-posted] > '11/1/2007' 

[jhardman]

Oh, duh! The problem is all the "OR"s This is interpreted as only having a date restriction on the first section (when category like '3'), the other categories have no date restrictions. To keep this same format, you would need to write

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND Events.Category LIKE '3' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '4' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '5' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '6' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '7' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '8' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '9' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '10' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '11' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '12' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '13' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '14' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '15' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '16' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '17' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '18' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '19' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '20' OR EventDateBegin >'01/01/2008' AND Events.Category LIKE '21' ORDER BY EventName" 

This would definitely work, but it is extremely unwieldy. Tell me more about the Category field and I might be able to suggest a better way to write this.

Jared

[jhardman]

OK, Nicodemas is definitely right. I guess I should learn to trust him. I got it to work just fine. I don't use parentheses in my query, but I would be surprised if that affects anything. Here is my query:

SELECT * FROM [works] WHERE [date-posted] > '11/1/2007' 

I don't know why I was thinking you used parentheses, but maybe they would help. I don't consider myself a SQL expert, but I might be able to write this better.

Jared

[jhardman]

OK, I looked it up, adding parentheses like this clears it up:

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND (Events.Category LIKE '3' OR Events.Category LIKE '4' OR Events.Category LIKE '5' OR Events.Category LIKE '6' OR Events.Category LIKE '7' OR Events.Category LIKE '8' OR Events.Category LIKE '9' OR Events.Category LIKE '10' OR Events.Category LIKE '11' OR Events.Category LIKE '12' OR Events.Category LIKE '13' OR Events.Category LIKE '14' OR Events.Category LIKE '15' OR Events.Category LIKE '16' OR Events.Category LIKE '17' OR Events.Category LIKE '18' OR Events.Category LIKE '19' OR Events.Category LIKE '20' OR Events.Category LIKE '21' ORDER BY EventName)" 

However if Category isn't a text field it could be simplified much more.

Jared

[xoozlez]

OK, I looked it up, adding parentheses like this clears it up:

strSQL = "SELECT EventID, EventName, EventDateBegin, EventDateEnd, EventTimeBegin, Category FROM Events WHERE EventDateBegin >'01/01/2008' AND (Events.Category LIKE '3' OR Events.Category LIKE '4' OR Events.Category LIKE '5' OR Events.Category LIKE '6' OR Events.Category LIKE '7' OR Events.Category LIKE '8' OR Events.Category LIKE '9' OR Events.Category LIKE '10' OR Events.Category LIKE '11' OR Events.Category LIKE '12' OR Events.Category LIKE '13' OR Events.Category LIKE '14' OR Events.Category LIKE '15' OR Events.Category LIKE '16' OR Events.Category LIKE '17' OR Events.Category LIKE '18' OR Events.Category LIKE '19' OR Events.Category LIKE '20' OR Events.Category LIKE '21' ORDER BY EventName)" 

However if Category isn't a text field it could be simplified much more.

Jared

Hey Jared! GREAT! It works! Thank you so much for your effort and help in this. I am really so happy. :)

Ok just a little bit more. I like to enhance the dateformat such that it is showing DDMMYY. You got any idea how should I change it? As the current results is showing MMDDYY

[jhardman]

Hey Jared! GREAT! It works! Thank you so much for your effort and help in this. I am really so happy. :)

Ok just a little bit more. I like to enhance the dateformat such that it is showing DDMMYY. You got any idea how should I change it? As the current results is showing MMDDYY

I believe that is actually a system setting (on the server), not set in your scripts but it could possibly be set in the db. I always try to manipulate dates in a non-ambiguous format like "12 Jan 2008" which most applications will recognize as a valid date and they can recognize and store it according to the preference of their program. As far as ASP goes, unless you want to write your own function for converting the date to the format you prefer, you are limited to the formatDateTime() function, and that still arranges according to system preference. Here's a link. This page says it converts to mm/dd/yy, but I think if your system preference is set the other way it goes to dd/mm/yy

Jared

[xoozlez]

Thank you Jared!

I manage to get it. In case it might be of some help to few of you. This is the code I am using.

varEventDate = FormatDateTime(CDate(rs("EventDateBegin")),1)


Cheers!