where date between, help please!

rstelma · July 20th, 2005, 11:58 AM

Hi Morpheus,

This is a tough one because I use the BETWEEN function all the time and never have a problem with it.

Could you post your the CREATE table code for your table here?

Also, what service pack are you running on this server, not that I know for sure if that would have any effect but it might have an effect on which collation type you're using and which language. Please provide that info as well.

Thanks,
Richard

rgerald · July 20th, 2005, 01:00 PM

Since you are talking about an ASP page, I think the proper syntax will be:

Code:

SQLStr="SELECT * FROM ABNMag where date between ''01/1/2005'' and ''12/25/2005'' order BY MagID DESC"

The string should be delimited by double-quote characters. "
The dates should be delimited by two single-quote characters. ''

When the string is parsed by SQL-Server the two single-quotes will be interpreted as a single single-quote character (field delimiter) instead of as a string delimiter.

Rand

morpheus · July 21st, 2005, 09:06 AM

Hi Rand, I get a syntax error when I try to use the two-single quotes.

@Richard, The table script is working fine, as it dose return some results correctly. Let's say for year 2004 I have 5 records, but only 4 will show. The fifth one is 10/25/2004, but the other 4 are 9/12/2004 or similar and they show up fine. I am not sure what service pack the database is as it is provided by my isp on a remote computer. Is there anyway to check through enterprise manager?

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason

rstelma · July 21st, 2005, 05:08 PM

Hi,

I was wondering if you could post the CREATE table script so that I could put it on my SQL server to test your query and code. Ultimately, I don't think this is a code thing. Your original SQL statement seemed fine to me. There could be a configuration problem with the server but since you have no control over it there is little that can be done there anyway. I'm sorry that you're having trouble with something that is usually really straight forward and simple.

Richard

pgtips · July 22nd, 2005, 04:30 AM

Maybe the server is interpreting your dates incorrectly? You say that dates like 9/12/2004 work OK, but did you notice that date is valid even if the server expectes either d/m/y or m/d/y? Whereas the date that doesn't work 10/25/2004 is only valid if its m/d/y.

Try either:
1. using the CONVERT statement to explicitly state the format of the dates
e.g. SELECT * FROM ABNMag where [date] between CONVERT(datetime, '01/01/2005', 101) and CONVERT(datetime, '12/25/2005' , 101) order BY MagID DESC
2. make the SQL string into a stored procedure with two datetime parameter objects and use the ADO Command object to call the procedure.

hth
Phil

happygv · July 22nd, 2005, 02:18 PM

Why not follow 'yyyy-mm-dd' format? That should bring you out of trouble, than following the format that you use now. Never give a chance to the server to interpret it incorrectly.

Cheers

_________________________
- Vijay G
Strive for Perfection

morpheus · July 26th, 2005, 07:10 AM

Sorry for the delay It has been crazy around here. I actually got phil's convert statment to work properly, so thanks for that :)

@happygv- if I leave the column in the DB set to datetime it wont let me use the date in the format you specified and I was getting an error with the between statement if I used the column as a varchar.

Thanks for all your help

-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason