SQL Server Performance Forum – Threads Archive
Use Time in SQL (problem)
I have table name AAA.<br />Id ti<br />1 9:00:01AM<br />2 10:00:01AM<br />3 10:12:45AM<br />4 10:30:01AM<br /><br />The type of time that I use in column ti is date/time hh:nn<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />sAM/PM<br /><br /><!–#include file="connect.asp"–><br /><%<br />sql="select * from AAA where ti < ’10:00:03AM’ "<br />set rst5=server.createobject("adodb.recordset")<br />rst5.open sql,conn,1,3<br />if not rst5.eof then<br />response.write "OK"<br />end if<br />%><br /><br />When I run this code I got message "Data type missmatch" <br />Then I tried ti < 10:00:03AM It still didn’t work!The time data type include yy/mm/dd, I can’t see that in your table.
What I’m missing?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
May I say that
– doing a SELECT * is _generally_ a bad idea in production code
– doing adhoc querying isn’t really better and
– rst5.open should better be done with a command object. Having that said:
What you see in your (I assume) datetime column is not stored there in the same way.
You might want to read this:
http://www.karaszi.com/sqlserver/info_datetime.asp
for general information on how to deal with temporal data in Sql.
———————–
–Frank
http://www.insidesql.de
———————–
Thank. It work.
It should be
ti < #10:00:03AM# But I still have question ?????
Assume that all of time was kept in variable which is array like this tiorder2(t2)=rst5("tiorder")
tiorder2(t2)
How can I write it…. sql="select top 1 * from ("&namecompany2(i2)&") where price <= "&limitprice2(j2)&" and ti > #&tiorder2(t2)#" Not work Note that tiorder2(t2) keep time
]]>