How to pass the Datetime value as parameter to a stored procedure?

Question

In the table, I have a column called as BillDate (Datetime). The datetime is stored in the format YYYY-MM-DD (Example: 2012-01-01 00:00:00.000). Now I want to retrieve the values between the range of datetime values. When I used query as the following I could not get the result:

SELECT * FROM RequestHeader
WHERE 
  CONVERT(VARCHAR, RH.BillDate ,105) BETWEEN CONVERT(VARCHAR, @FromDate,105) 
  AND CONVERT(VARCHAR,@ToDate , 105)

---

@FromDate is passed as : '2012-01-01'
@ToDate is passed as : '2012-01-01'

Answer 1

Try this:

SELECT * FROM RequestHeader
WHERE 
  CONVERT(VARCHAR, RH.BillDate ,105) BETWEEN CONVERT(VARCHAR, CAST(@FromDate AS DATETIME),105) 
  AND CONVERT(VARCHAR, CAST(@ToDate AS DATETIME), 105)

Unfortunately, using this method doesn't utilize indexes, so if you're hoping that you'll have fast query execution in the event that your date columns are indexed, you won't feel it here.