select * from (select
CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail where testTypes='2') as temp
where [NewPartDate]>GETDATE()
解释下 视图里的 PartDate是月日的形式 由于存储时可能是12-1这种形式而正常的数据库日期是12-01 所以在直接跟日期比较时会出现范围不准确的问题,才有上边的CAST转换,不加where查询是没问题的,但是在进行where查询时就会出现“从字符串转换日期和/或时间时,转换失败。” 期间尝试过转换成varchar比较也是同样的错误,望哪位大侠指教下。
CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail where testTypes='2') as temp
where [NewPartDate]>GETDATE()
解释下 视图里的 PartDate是月日的形式 由于存储时可能是12-1这种形式而正常的数据库日期是12-01 所以在直接跟日期比较时会出现范围不准确的问题,才有上边的CAST转换,不加where查询是没问题的,但是在进行where查询时就会出现“从字符串转换日期和/或时间时,转换失败。” 期间尝试过转换成varchar比较也是同样的错误,望哪位大侠指教下。
CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail where testTypes='2') as temp
where '2010-'+[PartDate]>GETDATE()
(
select CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail where testTypes='2'
) as temp
where isdate([NewPartDate])=0看看上面结果
CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail
where testTypes='2'
and '2010-'+[PartDate]>GETDATE()
CAST(('2010-'+[PartDate]) as datetime) [NewPartDate]
from V_HouseAndRateDetail where testTypes='2') as temp
where CAST(('2010-'+[PartDate]) as datetime)>GETDATE()
select 'aaa' aaa from V_HouseAndRateDetail
where (testTypes='2')
) as temp where aaa like '%aa%'
这个语句可以正常执行
(select [newPartDate]=case when cast(('2010-'+[PartDate]) as datetime)
between DATEADD(mm, DATEDIFF(mm,0,'2010-12-1'), 0)
and dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,'2010-12-1')+1, 0))
then 0
else 1 end
from V_HouseAndRateDetail where (testTypes='2') )
as temp where newPartDate=0
连这样都出错纠结啊