date format dumb question

Question

Post reply

date format dumb question

Casey Christensen

Old Hand

Points: 364
More actions
September 15, 2005 at 10:59 am

#89043

Okay, I'm having a serious brain fart today. I have a webapp that displays some results out of a db. I've got this string var
querystring = "select * from trans where date >= 05/01/2005"
I pass this string variable and the connection to my data adapter and the results are fine. Problem is it's not filtering on the date correctly. I've tried changing the format of the date and can't get it to actually filter out the correct dates.
Yes, the column name is "date", and no, I didn't do it and can't change it
Thanks in advance,
Casey
Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

<quote>Yes, the column name is "date",<end quote>

What database are you using? SQL Server doesn't have a "date" datatype. Datetime, but not Date.

Also, it would help if you let us know what you mean by "it's not filtering on the date correctly." Maybe give us a sample of what is returned and how you think you should be seeing it.

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

"select * from trans where date >= '05/01/2005'"

might be better to use a sp instead of dynamic sql too .

Casey Christensen Old Hand Points: 364 More actions · Answer 3

Sorry, I didn't give enough info in my first post...

The database is access 2.0, I'm using SQL syntax from inside an ASP.NET webapp. I can't use an sp because I'm not actually using sql server, and the query string is dynamically generated by the app with parameters the user chooses. For testing purposes, I have set the string to "select * from trans where date >= 05/01/2005"

I'm not sure if the query is returning all rows in the trans table, but it is returning results from March of 05 and 2004.

I have tried putting single quotes around the date, but that causes a data type mismatch compilation error.

I also know the query is getting applied to the result set, because if I change the where clause to match on a string field, the results are correct.

Thanks again,

Casey

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Anyway you can upgrade to access 2K at least??????????????

Never worked with anything under 1997 and even that was a pain.

Casey Christensen Old Hand Points: 364 More actions · Answer 5

Unfornunately, no. It's the backend of an app we got stuck with. (And in addition to the access 2.0 db, the app is written badly enough to warrant me writing an entire webapp to bypass its reporting.)

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Try replacing the quotes with wrapping # (#2005/01/01#).

Casey Christensen Old Hand Points: 364 More actions · Answer 7

Hey, that works great! Thanks a lot.

Two muffins are sitting in an oven. The first one turns to the second and says "pretty hot in here, huh?"
The second muffin glances at the first and then shrieks in fear,
"AAAAAAHH!!! A TALKING MUFFIN!!!"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Just a lucky guess, that's how it works in 2k. It looks like this is an old standard .