problem with query on a Datetime field 1

[bobyjose]

Hi all,
I have a problem with a query on a Datetime field.
pls go thorugh the details.

Table name - HOLIDAYLIST
Fields in table.
1. HolidayName (varchar)
2. HolidayDate (Datetime)

Data in table (only one row is present)

'My Holiday', 04/01/2001:00:00:00:00 (take it as dd-mm-yyyy)

Query
---
SELECT * FROM HolidayList
WHERE CONVERT(VARCHAR(12), HolidayDate, 105)
BETWEEN '05-12-2000' AND '11-01-2001'

Problem
------
The above query is not returning any rows, though 4-1-2001
falls in the given range.

Obeservation
----------
If you give the day (of the start date) any value
from 01 to 04, (eg: 01-12-2000 instead of 05-12-2000)
the query fetches the row with the date 04-01-2001

Could you please tell me what is wrong with this query and what is the correct way of doing it?
Thanx in advance,
Boby.

 

[TomSark]

Try doing this to see if it will solve your problem:

SELECT * FROM HolidayList
WHERE CONVERT(VARCHAR(12), HolidayDate, 105)
BETWEEN '12/05/2000' AND '1/11/2001'

I tried your query on my server and found that SQL Server is converting the string literals in your between to mm/dd/yyyy format even though your convert uses style 105 (dd-mm-yyyy).

Hope this helps Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[hirick]

When you convert a date to a varchar, you are doing string comparison, not date comparison. So a string starting with '04' is not between '05xxx' and '11xxx'. The only way a string comparison will be accurate for dates is if you use a yyyy/mm/dd format.

Your selection should be:

set dateformat dmy
SELECT * FROM HolidayList
WHERE HolidayDate
BETWEEN '05-12-2000' AND '11-01-2001'

The set dateformat command tells SQL what order to parse your date. The default is mdy.

To set your SQL server to always use day-month-year, see article Q173907 on MSDN.