count by date - 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 by date

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-08-23 : 09:20:57

how can i count by date instead of time? i already convert the date but it still count the time as well.

select dateofbirth, id from tableA

dateofbirth id
2012-07-31 23:01:19.000 123
2012-08-01 00:32:16.000 546
2012-08-01 01:21:17.000 988
2012-08-01 01:48:14.000 123
2012-08-01 02:07:28.000 654

the result that i want is count how many same date.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-08-23 : 09:51:43

For SQL 2008 or 2012
SELECT convert(date,dateofbirth) as Bday,count(*)
FROM tableA
GROUP BY convert(date,dateofbirth)

For 2005
SELECT convert(varchar(10),dateofbirth,101) as Bday,count(*)
FROM tableA
GROUP BY convert(varchar(10),dateofbirth,101)

Jim

Everyday I learn something that somebody else already knew

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-23 : 09:53:46

select dte = convert(varchar(8),dateofbith,112), count(*)
from tbl
group by convert(varchar(8),dateofbith,112)

or more efficient but less readable

select dte = dateadd(dd,datediff(dd,0,dateofbith),0), count(*)
from tbl
group by dateadd(dd,datediff(dd,0,dateofbith),0)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-23 : 10:07:25

i dont like idea of converting to varchar for neglecting timepart. It will have issues later if you're using this value for any of date manipulations. i usually do it using dateadd,datediff logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-23 : 10:09:44

quote:
Originally posted by visakh16

i dont like idea of converting to varchar for neglecting timepart. It will have issues later if you're using this value for any of date manipulations. i usually do it using dateadd,datediff logic

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If you stick to style 112 it will be implicitely converted unambiguously to a dataetime for any date arithmetic and also be sorted in date order so there shouldn't be any issues.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources