grouping by a date of DATETIME type

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

SQL Server 2000 Forums

SQL Server Development (2000)

grouping by a date of DATETIME type

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-31 : 06:02:10

i want to make a group in a query by dates,
where the time has no matter for me.
for example :


select count(id),companyid,entrancedate from tbl_entrance
group by companyid,entrancedate

where the "entrancedate" - i want to be group by the date fo example todays is : 31/12/2006 and ignoring the time!
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-31 : 07:11:24

remove the timeportion:

dateadd(day,datediff(day,0,entrancedate),0)

rockmoose

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-12-31 : 07:51:03

nice way
1 more question :
how can i for each day count how many companies appear in that day (i need to count every company once never mind how many times the company appear in that column)
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 07:53:05

Combine the two queries

select count(distinct companyid), dateadd(day,datediff(day,0,entrancedate),0) from tbl_entrance
group by dateadd(day,datediff(day,0,entrancedate),0)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources