DateTime ; between query in SQL SERVER 2000

[Karan_TN]

hi, currently i m using sql server 2000. i declared one column as "startdate" and the value as follows...

startdate="23-Feb-2010 4:00PM"
startdate="24-Feb-2010 4:00PM"

when i query as below i m not getting any record.

select * from tablename where startdate between '23-Feb-2010' and '23-Feb-2010'

can anyone help me? - KARAN

[WoutL]

There aren't any dates between 23 feb 2010 and 23 feb 2010. Thats why youre query doesn't return anything. Try:

select * from tablename where startdate between '23-Feb-2010' and '24-Feb-2010'

Wout Louwers

[Luc Pattyn]

Read the documentation for your database. http://www.w3schools.com/sql/sql_between.asp[^] says: "In some databases, persons with the LastName of "Hansen" or "Pettersen" will not be listed, because the BETWEEN operator only selects fields that are between and excluding the test values). In other databases, persons with the LastName of "Hansen" or "Pettersen" will be listed, because the BETWEEN operator selects fields that are between and including the test values)." :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

---

[Luc Pattyn]

The idea is good, the suggestion isn't. There still isn't any date between those when treated as exclusive. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

---

[WoutL]

His startdates where:

startdate="23-Feb-2010 4:00PM"
startdate="24-Feb-2010 4:00PM"

So the first one is between the 23th and the 24th, isn't it?

Wout Louwers

[Luc Pattyn]

sorry, you're right. I would have given an example with a broader range though. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

---

[Karan_TN]

i have gave my start date's. while i remove the timing, its work fine. but when the timing is included it fails. so when i m storing datetime, i always want to do in sepearate way only? for example want to create columns as... Startdate-StartTime, EndDate-EndTime,MaxDate-MaxTime....?? help me - Karan

[Luc Pattyn]

. No store date/time information as real DateTime, not as string, not as separate date and time. But be aware that "between" may or may not exclude the values you specify, i.e. depending on the database "BETWEEN a and b" means "a <= x <= b" or "a < x < b" :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

---

[Chris Meech]

On Oracle you could try

select * from tablename where trunc(startdate) between '23-Feb-2010' and '23-Feb-2010'

There is likely an equivalent for SQL Server. [EDIT] I'd also add that you are asking for a compare between two different database types so an implied conversion will take place. That conversion often causes results to be different than expected. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

[Adam Jasper]

Try this:

select * from tablename where (datediff(day, startdate, '23-Feb-2010') = 0)

Adam