where date between, help please!

morpheus · July 15th, 2005, 09:15 AM

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

Can you please tell me why this dose not work.

Thanks :)

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

rgerald · July 15th, 2005, 09:39 AM

Try it this way:

Code:

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

Note the use of single quotes and two-single-quotes.

Rand

rstelma · July 15th, 2005, 11:46 AM

Hi,

What exactly do you mean by " not work"?

Does it throw an error or not pull any records?

I tried naming a colmun "Date" just to see if this reserved word was causing the problem and it worked fine. I'm always very cautious about using reserved words since they can tend to cause unexpected problems.

Thanks,
Richard

rgerald · July 15th, 2005, 12:16 PM

This one works - just change the table and field names:

Code:

DECLARE @SQL varchar(1000)
SET @SQL = 'SELECT * FROM t_FEDonation WHERE DonationDate BETWEEN ''01/01/2004'' AND ''03/30/2004'''
EXEC(@SQL)

Note the use of single, double and triple single-quotes.

Rand

ashu_from_india · July 15th, 2005, 12:16 PM

hi

use it like this

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

Ashu

morpheus · July 18th, 2005, 07:14 AM

Thanks for the responses :)

I changed the date column in the DB to datetime as it was a varchar. But now the column lenght is fixed on 8. So I thought I could enter the date like this: 01/01/05 but it changes it too 01/01/2005. When I use the date between it wont pick up any date longer than 8 characters.

so this works 1/1/2005 but this dosent 1/25/2005, I am not sure what to do, here is the sql I am using.

SQLStr="SELECT * FROM ORMag where date between '01/01/2005' and '12/31/2005' order BY IssueNumber DESC

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

rstelma · July 18th, 2005, 11:47 AM

Morpheus,

Is this Microsoft SQL server? If this is MS-SQL then, I believe that the '8' in data type in the length fiedld refers to the eight bytes that the database uses to store the date. I have dates stored in my DB in the same format that you are using, 'mm/dd/yyyy'. I have no problem with using BETWEEN selecting dates. All of the dates are pulled correctly. I think you have a very interesting problem here. However, I don't know what could be wrong yet. I hope this can get resolved here. Something's whacky.

"SELECT * FROM TVA WHERE (PurchaseDate BETWEEN '1/1/1990' AND '8/3/2005');" pulls dates with '12/12/1996' and '1/1/2003'

I tried using the doulbe single quotes as suggested by rgerald and I got an "Incorrect Syntax" error.

I even tried naming a column 'Date' as you did and I didn't have a problem with that either.

Richard

rgerald · July 18th, 2005, 12:17 PM

Are you working directly with the database? ie. Query Analyzer with MS SQL Server or are you calling the data from a routine in VB or C# or something else. This will have a profound impact on which syntax is required.

Rand

rgerald · July 18th, 2005, 12:20 PM

The syntax suggested works with MS-SQL Server and Query Analyzer where the dates are stored in the database as datetime data types. If you are using char or varchar data types, the between syntax WILL NOT WORK unless the dates are formatted yyyymmdd or some variation with dashes, periods or slashes such as yyyy/mm/dd which requires a bigger field varchar(10).

Rand

morpheus · July 20th, 2005, 07:51 AM

Okay, I am using MS-sql and I am calling the date between from an asp page for the web. I have the field set as datetime in the db. So will I need to change the date format as suggested by rgerald?

Thanks again for your help

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