SQL Server Performance Forum – Threads Archive
convert datetime
Hello guys,<br /><br />I have a smalldatetime field in my table. I want to get the time of that smalldatetime field, and verify if it’s between a start and end time that I have. Here’s what I’m doing but it doesnt seem to work:<br /><br />WHERE MY_SMALLDATETIME_FIELD BETWEEN CONVERT(SMALLDATETIME, ’09:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />AND CONVERT(SMALLDATETIME, ’11:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />;<br /><br />and this is the value I have in MY_SMALLDATETIME_FIELD : 11/1/2006 10:00:00 AM<br /><br />Does any one have a clue what i’m doing wrong? Because the query isn’t returning any results even though 10AM is between 9 and 11I think you need to read the defination of SMALLDATETIME. You are not using date in your convert function…<br /><br />WHERE MY_SMALLDATETIME_FIELD BETWEEN CONVERT(SMALLDATETIME, ‘2006-12-01 10:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />AND CONVERT(SMALLDATETIME, ‘2006-12-01 11:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />;<br /><br />Here is the info from BOL…<br />datetime<br />Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.<br /><br />smalldatetime<br />Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.<br /><br /><br /><br /><br /><br /><br />Mohammed U.
Change your WHERE clause to:<br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, MY_SMALLDATETIME_FIELD, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> BETWEEN CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ’09:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />AND CONVERT(char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ’11:00:00′, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />;<br /></font id="code"></pre id="code"><br />You might also find this interesting:<br /<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
Btw, relying on implicit conversion this will also work:<br /><pre id="code"><font face="courier" size="2" id="code"><br />WHERE CONVERT(CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, MY_SMALLDATETIME_FIELD, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> BETWEEN ’09:00:00′ AND ’11:00:00′;<br /></font id="code"></pre id="code"><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
http://www.sql-server-performance.com/fk_datetime.asp fyi on datetime gotchas. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
thanks for ur help guys, it worked <img src=’/community/emoticons/emotion-3.gif’ alt=’
‘ />)<br /><br />Mike
]]>