SQL Server Performance Forum – Threads Archive

Finding data registered on a spesific date.

I want to find all data from my table that has a spesific date in the date column, but my select-query only results in this error-message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. What can I do to get the result I want??
[?]

---

Post the query you used Madhivanan Failing to plan is Planning to fail

---

Show us the written date in your WHERE clause. Also, T-SQL has the nasty habit of refusing to cope with dates before the year 1751.

---

select dv.pid as personid, p.name, dv.diagnose from doctorvisit dv
inner join person p on dv.pid = p.pid and dv.dato = ‘15.05.2001’;

---

Sorry..I wrote the query wrong:
select dv.pid as personid, p.name, dv.diagnose from doctorvisit dv
inner join person p on dv.pid = p.pid where dv.dato = ‘15.05.2001’;

---

For written out dates, use either the yyyy-mm-dd format (‘2001-05-15′), or the mm/dd/yyyy format (’05/15/2001’). These are the only two formats that SQL Server really understands, and it doesn’t really matter what’s on the Windows regional settings of the SQL Server machine.

---

Thanks. Now it works perfectly!!!

---

You’ve got your solution, but in case your date column contains any time portion != 00:00:00.000 (that is midnight), you would need to strip out the nasty time to get accurate results. In that case you might want to check out:http://www.sql-server-performance.com/fk_datetime.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

In addition to what Frank says, seeing that you have the . delimiter on the date, you probably have a . delimiter for the time part as well. Note that SQL Server only understands a colon (<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> as the time delimiter.

---

]]>