By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Query past events of 2007

P: 9
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?
Jan 4 '08 #1
Share this Question
Share on Google+
14 Replies


jhardman
Expert 2.5K+
P: 3,405
The date should be enclosed in pound signs:
Expand|Select|Wrap|Line Numbers
  1. EventDateBegin >#01/01/2008#
Let me know if this helps.

Jared
Jan 5 '08 #2

Expert 100+
P: 164
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?
Jan 6 '08 #3

P: 9
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.
Jan 7 '08 #4

P: 9
Hi there, any help? :)
Jan 8 '08 #5

jhardman
Expert 2.5K+
P: 3,405
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
Jan 8 '08 #6

P: 9
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

:)
Jan 8 '08 #7

jhardman
Expert 2.5K+
P: 3,405
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
Jan 8 '08 #8

jhardman
Expert 2.5K+
P: 3,405
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM [works] WHERE [date-posted] > '11/1/2007' 
Attached Images
File Type: gif screen1.gif (18.8 KB, 112 views)
Jan 8 '08 #9

jhardman
Expert 2.5K+
P: 3,405
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
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 8 '08 #10

jhardman
Expert 2.5K+
P: 3,405
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 8 '08 #11

jhardman
Expert 2.5K+
P: 3,405
OK, I looked it up, adding parentheses like this clears it up:
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 8 '08 #12

P: 9
OK, I looked it up, adding parentheses like this clears it up:
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 9 '08 #13

jhardman
Expert 2.5K+
P: 3,405
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
Jan 9 '08 #14

P: 9
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!
Jan 10 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.