Count without DateTime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count without DateTime

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-21 : 21:18:55

I would like to SUM a total of category but need to publish the date as well. How can I SUM the total of category without SUM the date?

Select category, count(*) as Total, date
from tablename

but when i run the query, the output is like this :

CATEGORY TOTAL DATE
car 1 2011-12-21 12:20:03
car 1 2011-12-21 12:19:09
car 1 2011-12-21 12:30:15
bus 1 2011-12-21 12:29:20
bus 1 2011-12-21 12:20:54

I would like the output to be like this:

CATEGORY TOTAL DATE
car 3 2011-12-21
bus 2 2011-12-21

Is it possible?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-21 : 21:30:43

[code]
select CATEGORY, COUNT(*) as Total, DATEADD(DAY, DATEDIFF(DAY, 0, date), 0) as DATE
from tablename
group by CATEGORY, DATEADD(DAY, DATEDIFF(DAY, 0, date), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-21 : 21:45:32

Great!
So if just want to publish the date only i need to convert?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-21 : 21:46:58

quote:
Originally posted by peace

Great!
So if just want to publish the date only i need to convert?

what do you mean by "publish" ?

KH
[spoiler]Time is always against us[/spoiler]

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2011-12-21 : 21:49:38

Currently it appeared the date and the time.
How can I convert just to appear the date only.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-21 : 22:09:29

this will "remove" the time part or set it to 00:00

DATEADD(DAY, DATEDIFF(DAY, 0, date), 0)

if you are referring to displaying the result in date format without the time, this is just formatting / presentation issue and should be done at your front end application or reporting tool.

If you have to do this in T-SQL, you can use CONVERT() function to convert to your required date format. But doing this means, you are returning a fomatted date string to your application and not date or datetime data type.

If you are using SQL 2008, you can use the DATE data type


select CATEGORY, COUNT(*) as Total, convert(date, [date]) as DATE
from   tablename
group by CATEGORY, convert(date, [date])

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources