修剪日期字段的一部分以按查询执行计数和分组 [英] Trim part of date field to perform a count and group by query

问题描述

你好：



我有下表

Hello:

I have the following table

EnteredDate              Item

1/2/2014 12:01:59 PM.    Bags

1/2/2014 12:02:54 PM.    Bags

1/3/2014 12:04:55 pm.    Shoes

我想返回以下数量：

I want to return the following count:

1/2/2014 Bags  2
1/3/2014 Shoes 1

我可以选择并计数查询，但因为我的数据以长日期格式存储，我可以按日期分组，除非我修剪时间部分。可以在SQL查询中完成吗？

I can do the select and count query but because my data is stored in long date format I can,t group by date unless I trim the time part. Can that be done in SQL query?

推荐答案

你可以使用强制转换并将其强制转换为格式'103'，例如



http://technet.microsoft.com/en-us/library/ms174450。 aspx [ ^ ]



'g'

can you use a cast and cast it to format '103' for example

http://technet.microsoft.com/en-us/library/ms174450.aspx[^]

'g'

试试这个..

Try this..

declare @table table
(
EnteredDate datetime , Item varchar(55)
)
insert into @table( EnteredDate , Item ) values ( GETDATE(), 'Bags')
insert into @table( EnteredDate , Item ) values ( GETDATE(), 'Bags')
insert into @table( EnteredDate , Item ) values ( GETDATE()-1, 'Shoes');

with T as (select CONVERT(varchar(10), EnteredDate, 103) as EnteredDate ,item from @table)
select EnteredDate,item, COUNT(item) as [Count] from T group by EnteredDate, Item

这篇关于修剪日期字段的一部分以按查询执行计数和分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！