Click here to Skip to main content
15,887,746 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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


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?
Posted

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

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

'g'
 
Share this answer
 
Try this..


SQL
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900