SQL Server Performance Forum – Threads Archive

Convert Function

I am writing a query on a table having yearly data. When I applied convert function on closedate field type datetime then it will shows the records of previous years
— closedate filed holding date and time both
select * from table1 where convert(char,closedate,101) between ’05/01/2007′ and ’05/03/2007′
It shows the records of the year 2006 for the same days lies in between operator. If I removed the convert function then it shows records for onlr two days means the records from ’05/03/2007′ is not shown.
select * from table1 where closedate between ’05/01/2007′ and ’05/03/2007′

---

You’re asking SQL Server to compare a date string (not the date value) to a written date. Range criteria for strings work left-to-right, so the start of the string is the most significant part. In your format, you’re finding all May 1st, 2nd and 3rd dates in any given year. Use the 120 switch for CONVERT, and the yyyy-mm-dd format for spelling out the dates.

---

I have found a that if use cast function over convert function on the same query then the correct result set will achieved select * from table1 where cast(convert(char,closedate,101) as datetime) between ’05/01/2007′ and ’05/03/2007′

---

Excellent work-around, as long as you understand how (and why) it works.[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

Refer
http://www.sql-server-performance.com/fk_datetime.asp Madhivanan Failing to plan is Planning to fail

---

]]>