如何去掉smalldatetime 类型的时间?

[风车呼噜噜]

如:把2009-08-30 17:00:01 变为2009-08-30 00:00:00,并且能在where 条件上用最好.

[华夏小卒]

select cast(cast('2009-08-30 17:00:01' as varchar(10) as datetime)

[华夏小卒]

select cast(cast('2009-08-30 17:00:01' as varchar(10)) as datetime)

modify

[liangCK]

WHERE date_col >= '2009-08-30' AND date_col < DATEADD(day,1,'2009-08-30');

[风车呼噜噜]

我还是说的更具体点吧,比如有这么些数据:
2009-08-30 08:00:00 A
2009-08-30 09:00:00 B
2009-08-31 08:00:00 C
2009-08-31 07:00:00 D
2009-08-29 06:00:00 E
我要按日期分组,并计算同一日期的记录数:
2009-08-30 00:00:00 2
2009-08-31 00:00:00 2
2009-08-29 00:00:00 2

[华夏小卒]

if object_id('tb')is not null drop table tb
go
create table tb(date datetime ,id varchar(20), )
insert tb select   
 '2009-08-30 08:00:00',    'A' union all select
  '2009-08-30 09:00:00',    'B' union all select
  '2009-08-31 08:00:00' ,   'C' union all select
  '2009-08-31 07:00:00'  ,  'D' union all select
  '2009-08-29 06:00:00'   , 'E' 

select cast(cast(date as varchar(10)) as datetime) as date,count(*)as num
from tb group by cast(cast(date as varchar(10)) as datetime)

date                    num
----------------------- -----------
2009-08-29 00:00:00.000 1
2009-08-30 00:00:00.000 2
2009-08-31 00:00:00.000 2

(3 行受影响)

[风车呼噜噜]

[Quote=引用 5 楼 js_szy 的回复:]
SQL codeifobject_id('tb')isnotnulldroptable tbgocreatetable tb(datedatetime ,idvarchar(20), )insert tbselect'2009-08-30 08:00:00','A'unionallselect'2009-08-30 09:00:00','B'unionallselect'2009-08-31 08¡­
[/Quote]

要的就是它,谢谢!

[devilidea]

学习