Datetime field in SQL Server

[dr00bie]

I have been trying to make this work all morning, nothing seems to work. I have a small table with 5 fields, ApptName, ApptDoctor, ApptDate, ApptTime, ApptNotes. I need to be able to filter this data by a date range. I have done this before, but now it is giving me problems. If I setup my table so that ApptDate is a datetime field, and run this query,

SELECT * 
FROM tblCliAppts 
WHERE ApptDate BETWEEN 10/01/2004 AND 11/31/2004

Then it gives me no results, even though I have 3 records that are between that date range.

If I setup ApptDate as a varchar, and run this query,

SELECT * 
FROM tblCliAppts 
WHERE ApptDate BETWEEN '10/01/2004' AND '11/31/2004'

It returns all records between that range, but if one of the dates is 11/01/2005 it returns that record as well. I have searched high and low and can't find anything about this.

Thanks in advance,
Drew

 

[jby1]

Have you tried it with ApptDate set up as a DateTime and using the single quotes around the dates?

 

[dr00bie]

It gives me this error if I try that...

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


Thanks,
Drew

 

[Pattycake245]

Try

SELECT *
FROM tblCliAppts
WHERE ApptDate BETWEEN '2004-10-04' AND '2004-11-30'

or

SELECT *
FROM tblCliAppts
WHERE ApptDate BETWEEN '10/04/2004' AND '11/30/2004'

And have the date as datetime. Doesn't November have only 30 days? I'm surprised you didn't get an out of date range error. Actually you are probably not getting the error because your dates aren't in quotes.

Tim

 

[dr00bie]

I was so wrapped up in the syntax I wasn't even looking at my date... yes that was the problem Nov only has 30 days...

here is the correct query

SELECT *
FROM tblCliAppts
WHERE ApptDate BETWEEN '10/01/2004' AND '11/15/2004'

thanks,
Drew