grouping by date

Question

Post reply

grouping by date

mathewspsimon

SSCommitted

Points: 1721
More actions
August 25, 2008 at 12:35 am

#196916

I have a date column and text column.when i do a grouping instead of taking date it takes the time also so for the same date i get 2 rows instaed of one like below
UserID Date
ABC 2008-02-08 11:09:26.620
ABC 2008-02-08 12:09:26.620
ABC 2008-02-15 10:09:26.620
I wan the output as
UserId Date Count
ABC 2008-02-08 2
ABC 2008-02-15 1
can some help me please

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

i6004835 Mr or Mrs. 500 Points: 529 More actions · Answer 1

select userID, (Month([Date]) + '/' + Day([Date]) + '/' + Year([Date])) as [onlyDate], count(*) as [Count]

from someTable

group by userID, (Month([Date]) + '/' + Day([Date]) + '/' + Year([Date]))

Ivan Budiono

mathewspsimon SSCommitted Points: 1721 More actions · Answer 2

Ivan when i did as you said I get error

Server: Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value '/' to data type int.

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 3

Please cast month, day and year into varchar to get rid out of it.

Dugi SSCoach Points: 17998 More actions · Answer 4

VERY EASY;

SELECT ID, CONVERT(VARCHAR, DATE, 103) AS YOURDATE, COUNT(ID_SOMETHING) FROM TABLE

GROUP BY ID, DATE

if you do not have any solution please post the definition of your table and some sample data and we will find solution for you !

Cheers!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

i6004835 Mr or Mrs. 500 Points: 529 More actions · Answer 5

Sorry for that. :p

Forget to add the CAST.

Change those with these ones:

CAST(MONTH([Date]) AS varchar(2))

CAST(DAY([Date]) AS varchar(2))

CAST(YEAR([Date]) AS varchar(4))

Other way to do the query is:

Select userID, CONVERT(varchar(10),[Date],101) as onlyDate, count(*) as [Count]

from someTable

Group By userID, CONVERT(varchar(10),[Date],101)

Ivan Budiono

j nuñez SSCrazy Points: 2175 More actions · Answer 6

I think the solution is:

SELECT UserID, CONVERT(VARCHAR, Date, 102) AS YOURDATE, COUNT(*)

FROM Table

GROUP BY UserID, CONVERT(VARCHAR, Date, 102)

Carl Federl One Orange Chip Points: 25384 More actions · Answer 7

There is a very long post titled "just the date, please" at http://www.sqlservercentral.com/Forums/Topic379596-8-2.aspx that, for various algorithms, confirms the validity and has performance comparisons.

The best solution consists of

1. Cast the datetime to a float

2. Cast the float to an integer

3. Cast the integer to a datetime

CAST(CAST(CAST(ADateTime as float) as integer ) as datetime) as DateOnly

Under SQL Server 2008, which has a date datatype, this very simple:

CAST( ADateTime as Date)

declare @Dates table (ADateTime datetime)

insert into @Dates (ADateTime )

select '2001-12-31 23:59:59.997' union all

select '2001-12-31 23:59:59.000' union all

select '2001-12-31 00:00:00.000' union all

select '2001-12-31 00:00:00.003'

SELECTADateTime

,CAST(CAST(CAST(ADateTime as float) as integer ) as datetime) as DateOnly

--,CAST( ADateTime as Date) as DateOnly2008

from @Dates

SQL = Scarcely Qualifies as a Language

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 8

Carl Federl (8/25/2008)
There is a very long post titled "just the date, please" at http://www.sqlservercentral.com/Forums/Topic379596-8-2.aspx that, for various algorithms, confirms the validity and has performance comparisons.
The best solution consists of
1. Cast the datetime to a float
2. Cast the float to an integer
3. Cast the integer to a datetime
CAST(CAST(CAST(ADateTime as float) as integer ) as datetime) as DateOnly

Thanks for the link to that post!! It is unbelievable. Someone should definitely write that one up into an article.