关于时间显示的问题

Question

1.数据表
findDate  
------------------------             
2010-08-10 00:00:01
2010-08-10 07:14:03
2010-08-10 08:15:00
2010-08-10 09:14:002.要达到的结果：
findDate               ShowDate
--------------------------------------------  
2010-08-10 00:00:01     2010-08-09
2010-08-10 07:14:03     2010-08-09
2010-08-10 08:15:00     2010-08-10
2010-08-10 09:14:00     2010-08-10规则为：
凡是本日 08:15:00     前的 ShowDate 为前天,否则ShowDate为当天

Answer 1

if object_id('tb')>0
drop table tb
create table tb
(
finddate datetime
)insert into tb 
select '2010-08-10 00:00:01'
union all 
select '2010-08-10 07:14:03'
union all 
select '2010-08-10 08:15:00'
union all
select '2010-08-10 09:14:00'
select finddate,(case when convert(varchar(10),finddate,120)+ ' 08:15:00.000' > finddate  then convert(varchar(10),dateadd(d,-1,finddate),120) else convert(varchar(10),finddate,120) end) as showdate
from tb
结果2010-08-10 00:00:01.000 2010-08-09
2010-08-10 07:14:03.000 2010-08-09
2010-08-10 08:15:00.000 2010-08-10
2010-08-10 09:14:00.000 2010-08-10