In Microsoft SQL Server, how can I group data from a datetime column
based solely on the DATE (but not the time). Consider the following
information:

09/20/05 03:15pm
09/20/05 03:16pm
09/20/05 03:17pm
09/21/05 11:30am
09/21/05 11:31am

The desired results are:

09/20/05 3 rows
09/21/05 2 rows

How can I use a CAST, CONVERT or similar operator such that the GROUP
BY clause in my SELECT statement will produce the desired output?

Thank you!

---

Clarification of Question by chrisasking-ga on 25 Sep 2005 16:49 PDT

The most recent suggestion seems to arrange the result set in date
order incorrectly, in that the rows are displayed by month as the
primary sort, rather than the year (in other words, 12/03/03 appears
at the top of the query, whereas 10/01/04 appears much lower down).
Can this be fixed?
Thanks

---

Clarification of Question by chrisasking-ga on 26 Sep 2005 08:16 PDT

This is great, thanks !!!

Hi

How abt the below qry

SELECT date1, count(date1) as rows from table1 group by date1

thanks
bala

SELECT  DATEPART (m, YourDateField) As Month, DATEPART (d,
YourDateField) as Day, DATEPART (yy, YourDateField) as Year, Count
(YourDateField) as Count, ' rows'  from YourTable

Group By DATEPART (m, YourDateField), DATEPART (d, YourDateField),
DATEPART (yy, YourDateField)

putting in the title of your date column in place of YourDateField and
your table name in place of YourTable.

That should work - shame I can't post it as an answer...  bala has the
right idea but didn't carry out the grouping to get rid of times...

No need to Cast or Convert, which will only confuse...

AG

The output displays the date and their counts, so I thought not
necessary to check the time.

Thanks
Bala

sorry, mine wont work.

thanks
bala

Hi

Check out this query.

SELECT  CONVERT(char(12), DATEFIELD, 1),count(CONVERT(char(12),
DATEFIELD, 1)),' rows' as result from TABLENAME group by
CONVERT(char(12), DATEFIELD, 1)

thanks
bala

This query will display by order.

SELECT  CONVERT(char(12), DATEFIELD, 1) as
date,count(CONVERT(char(12), DATEFIELD, 1)) as count ,' rows' as
result from TABLENAME group by CONVERT(char(12), DATEFIELD, 1) order
by CONVERT(char(12), DATEFIELD, 1) desc

DATEFIELD - your date field name
TABLENAME - your table name

Thanks
Bala

Hi chrisasking,

This query will give you the output. The problem is it will display
one more column like order1. Let me know what do you think.

SELECT CAST(Substring(CONVERT(char(12), curdate, 1) ,7,2) as int) as
order1, CONVERT(char(12), curdate, 1) as date,count(CONVERT(char(12),
curdate, 1)) as count ,' rows' as result from table1 group by
CONVERT(char(12), curdate, 1),CAST(Substring(CONVERT(char(12),
curdate, 1) ,7,2) as int) order by CAST(Substring(CONVERT(char(12),
curdate, 1) ,7,2) as int)

curdate - datefield
table1  - table name


Thanks
Bala

SELECT CONVERT(VARCHAR,date_field,1) as date_field
FROM table_name
GROUP BY CONVERT(VARCHAR,date_field,1);

You can also change the format of the date that comes out by changing
the third parameter. Here are the formats
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

Hi awilinsk,

you query very simple. great!

bala

hi,
convert your date column to varchar(10) and with style 103 or 3
and then use group by to find count

select convert(varchar(10),YourDate,103), 
count(*), ' rows' from YourTable 
group by convert(varchar(10),YourDate,103)

i think it will give u the result

Hi,
try my query:

select convert(varchar(10), <your date time>,103),
convert(varchar,count(*)) + ' rows'
from <your table> 
group by convert(varchar(10), <your date time>,103)

While:
<your date time> is your field/column represent the datetime value
<your table> is your table name.

For example:
select convert(varchar(10), myDate,103), convert(varchar,count(*)) + ' rows' 
from myTable 
group by convert(varchar(10), myDate,103)

Cheer..