输入如下语句:
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
结果显示:
"当前"SQL"窗格内容做语法分析时发生下列错误:
输入项无法被转换为有效日期时间值."
请各位路过的高手帮个忙!小弟在线等!
31 个解决方案
#1
datetime型的数据不能用LIKE查询
#2
WHERE SEND_TIME LIKE '%2007-6-8%'
-----------------------------------
不对
-----------------------------------
不对
#3
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) LIKE '%2007-06-08%'
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) LIKE '%2007-06-08%'
#4
SELECT * FROM SEND WHERE datetidd(dd,SEND_TIME,'2007-06-08')=0
#5
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) = '2007-06-08'
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) = '2007-06-08'
#6
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) LIKE '%2007-6-8%'
不过没心要这样
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) ='2007-06-8'
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) LIKE '%2007-6-8%'
不过没心要这样
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) ='2007-06-8'
#7
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#8
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#9
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#10
SELECT *
FROM SEND
WHERE convert(char(10),SEND_TIME,120) = convert(char(10),'2007-6-8',120)
FROM SEND
WHERE convert(char(10),SEND_TIME,120) = convert(char(10),'2007-6-8',120)
#11
:)
#12
SELECT *
FROM SEND
WHERE Convert(nvarchar(50),Convert(datetime,SEND_TIME),23) LIKE '2007-06-08'
FROM SEND
WHERE Convert(nvarchar(50),Convert(datetime,SEND_TIME),23) LIKE '2007-06-08'
#13
谢谢各位朋友的热情帮助,可是没有一个能成功的!
#14
时间查询不要用like 如果下面的不行,应该是其它问题(可能传入的数据),也不知道你的字段类型
SELECT * FROM SEND WHERE datediff(dd,SEND_TIME,'2007-06-08')=0
SELECT * FROM SEND WHERE datediff(dd,SEND_TIME,'2007-06-08')=0
#15
是的,前面是我糊涂了!
朋友们写的那些代码是可以运行的.
不过,我在ASP里加进去后,就不再有效!
我的部分代码是:
<%
dim times
times=request.Form("year")&"-"&request.Form("month")&"-"&request.Form("date")
if request.Form("action")="send_yxtime" then
sql="select * from send where send_yxtime like '%"×&"%'"
elseif request.Form("action")="send_time" then
sql="select * from send where send_time like '%"×&"%'"
else
sql="select top 1000 * from send order by send_time DESC"
end if
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3 %>
<tr>
<td><%
if rs.eof and rs.bof then
response.write "暂未加入栏目" %>
</td>
</tr>
还想请大家帮我看看!!!
朋友们写的那些代码是可以运行的.
不过,我在ASP里加进去后,就不再有效!
我的部分代码是:
<%
dim times
times=request.Form("year")&"-"&request.Form("month")&"-"&request.Form("date")
if request.Form("action")="send_yxtime" then
sql="select * from send where send_yxtime like '%"×&"%'"
elseif request.Form("action")="send_time" then
sql="select * from send where send_time like '%"×&"%'"
else
sql="select top 1000 * from send order by send_time DESC"
end if
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3 %>
<tr>
<td><%
if rs.eof and rs.bof then
response.write "暂未加入栏目" %>
</td>
</tr>
还想请大家帮我看看!!!
#16
在end if后加response.write times 或 msgbox(times)看看获得的是什么值
#17
当天的日期
#18
表中send_time的数据类型为:
smalldatetime
smalldatetime
#19
先帮顶一下,有事,用上面的方法,把sql显示出来,在查询分析器下执行,贴出来让大家分析吧
#20
谢谢朋友!
#21
最简单的方法,干嘛非得用DateTime,用NVarchar存就是了,返回的字串一样可以用的
一个网站的开发时间是很短的,如果在一个Time上浪费太多的时间实在是不划算
一个网站的开发时间是很短的,如果在一个Time上浪费太多的时间实在是不划算
#22
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-6-8'
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-6-8'
#23
把党中那段换成这样试试
应该可以了
sql="select * from send where Convert(nvarchar,send_yxtime,23) = Convert(nvarchar,Convert(datetime,'"×&"'),23)"
elseif request.Form("action")="send_time" then
sql="select * from send where Convert(nvarchar,send_time,23)= Convert(nvarchar,Convert(datetime,'"×&"'),23)"
应该可以了
sql="select * from send where Convert(nvarchar,send_yxtime,23) = Convert(nvarchar,Convert(datetime,'"×&"'),23)"
elseif request.Form("action")="send_time" then
sql="select * from send where Convert(nvarchar,send_time,23)= Convert(nvarchar,Convert(datetime,'"×&"'),23)"
#24
在.net里面是‘2007-6-8’在数据库里面是‘2007-06-08’
#25
谢谢各位朋友对我帮助,小弟在此谢过,由于分数不够给!
还请朋友们原谅!
还请朋友们原谅!
#26
convert(varchar(10),SEND_TIME,120) between date1 and date2
#27
直接用<,=,>符号就行了
#28
O_o
#29
这个sql需求是什么呀? 要清楚呢。。因为如果是=就可以了,建议不要用like呢,如果和“=”同价,那不走索引多慢呀。
所以最好先明确sql的需求,然后再拼合适的sql就好,别只为出结果呀,效率和需求都不管了呢。
所以最好先明确sql的需求,然后再拼合适的sql就好,别只为出结果呀,效率和需求都不管了呢。
#30
学习一下!
#31
试试这个 怎样
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-Jun-08'
或者
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME like '%2007-Jun-08'
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-Jun-08'
或者
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME like '%2007-Jun-08'
#1
datetime型的数据不能用LIKE查询
#2
WHERE SEND_TIME LIKE '%2007-6-8%'
-----------------------------------
不对
-----------------------------------
不对
#3
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) LIKE '%2007-06-08%'
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) LIKE '%2007-06-08%'
#4
SELECT * FROM SEND WHERE datetidd(dd,SEND_TIME,'2007-06-08')=0
#5
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) = '2007-06-08'
FROM SEND
WHERE convert(varchar(10),SEND_TIME,120) = '2007-06-08'
#6
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) LIKE '%2007-6-8%'
不过没心要这样
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) ='2007-06-8'
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) LIKE '%2007-6-8%'
不过没心要这样
SELECT *
FROM SEND
WHERE CONVERT(varchar(30),SEND_TIM,102) ='2007-06-8'
#7
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#8
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#9
SELECT *
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
FROM SEND
WHERE SEND_TIME LIKE '%2007-6-8%'
--------------------------------------
改成:
SELECT *
FROM SEND
WHERE convert(varchar(10),SEND_TIME,101) LIKE '%2007-6-8%'
#10
SELECT *
FROM SEND
WHERE convert(char(10),SEND_TIME,120) = convert(char(10),'2007-6-8',120)
FROM SEND
WHERE convert(char(10),SEND_TIME,120) = convert(char(10),'2007-6-8',120)
#11
:)
#12
SELECT *
FROM SEND
WHERE Convert(nvarchar(50),Convert(datetime,SEND_TIME),23) LIKE '2007-06-08'
FROM SEND
WHERE Convert(nvarchar(50),Convert(datetime,SEND_TIME),23) LIKE '2007-06-08'
#13
谢谢各位朋友的热情帮助,可是没有一个能成功的!
#14
时间查询不要用like 如果下面的不行,应该是其它问题(可能传入的数据),也不知道你的字段类型
SELECT * FROM SEND WHERE datediff(dd,SEND_TIME,'2007-06-08')=0
SELECT * FROM SEND WHERE datediff(dd,SEND_TIME,'2007-06-08')=0
#15
是的,前面是我糊涂了!
朋友们写的那些代码是可以运行的.
不过,我在ASP里加进去后,就不再有效!
我的部分代码是:
<%
dim times
times=request.Form("year")&"-"&request.Form("month")&"-"&request.Form("date")
if request.Form("action")="send_yxtime" then
sql="select * from send where send_yxtime like '%"×&"%'"
elseif request.Form("action")="send_time" then
sql="select * from send where send_time like '%"×&"%'"
else
sql="select top 1000 * from send order by send_time DESC"
end if
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3 %>
<tr>
<td><%
if rs.eof and rs.bof then
response.write "暂未加入栏目" %>
</td>
</tr>
还想请大家帮我看看!!!
朋友们写的那些代码是可以运行的.
不过,我在ASP里加进去后,就不再有效!
我的部分代码是:
<%
dim times
times=request.Form("year")&"-"&request.Form("month")&"-"&request.Form("date")
if request.Form("action")="send_yxtime" then
sql="select * from send where send_yxtime like '%"×&"%'"
elseif request.Form("action")="send_time" then
sql="select * from send where send_time like '%"×&"%'"
else
sql="select top 1000 * from send order by send_time DESC"
end if
set rs=server.createobject("adodb.recordset")
rs.open sql,conn,3,3 %>
<tr>
<td><%
if rs.eof and rs.bof then
response.write "暂未加入栏目" %>
</td>
</tr>
还想请大家帮我看看!!!
#16
在end if后加response.write times 或 msgbox(times)看看获得的是什么值
#17
当天的日期
#18
表中send_time的数据类型为:
smalldatetime
smalldatetime
#19
先帮顶一下,有事,用上面的方法,把sql显示出来,在查询分析器下执行,贴出来让大家分析吧
#20
谢谢朋友!
#21
最简单的方法,干嘛非得用DateTime,用NVarchar存就是了,返回的字串一样可以用的
一个网站的开发时间是很短的,如果在一个Time上浪费太多的时间实在是不划算
一个网站的开发时间是很短的,如果在一个Time上浪费太多的时间实在是不划算
#22
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-6-8'
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-6-8'
#23
把党中那段换成这样试试
应该可以了
sql="select * from send where Convert(nvarchar,send_yxtime,23) = Convert(nvarchar,Convert(datetime,'"×&"'),23)"
elseif request.Form("action")="send_time" then
sql="select * from send where Convert(nvarchar,send_time,23)= Convert(nvarchar,Convert(datetime,'"×&"'),23)"
应该可以了
sql="select * from send where Convert(nvarchar,send_yxtime,23) = Convert(nvarchar,Convert(datetime,'"×&"'),23)"
elseif request.Form("action")="send_time" then
sql="select * from send where Convert(nvarchar,send_time,23)= Convert(nvarchar,Convert(datetime,'"×&"'),23)"
#24
在.net里面是‘2007-6-8’在数据库里面是‘2007-06-08’
#25
谢谢各位朋友对我帮助,小弟在此谢过,由于分数不够给!
还请朋友们原谅!
还请朋友们原谅!
#26
convert(varchar(10),SEND_TIME,120) between date1 and date2
#27
直接用<,=,>符号就行了
#28
O_o
#29
这个sql需求是什么呀? 要清楚呢。。因为如果是=就可以了,建议不要用like呢,如果和“=”同价,那不走索引多慢呀。
所以最好先明确sql的需求,然后再拼合适的sql就好,别只为出结果呀,效率和需求都不管了呢。
所以最好先明确sql的需求,然后再拼合适的sql就好,别只为出结果呀,效率和需求都不管了呢。
#30
学习一下!
#31
试试这个 怎样
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-Jun-08'
或者
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME like '%2007-Jun-08'
SELECT *
FROM SEND
WHERE SEND_TIME = '2007-Jun-08'
或者
直接这样就行
SELECT *
FROM SEND
WHERE SEND_TIME like '%2007-Jun-08'