Format Long Date to Short Date in GroupBy

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

Transact-SQL (2000)

Format Long Date to Short Date in GroupBy

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-08-08 : 08:40:43

Hi

Here's my SQL:

SELECT containers.containercode AS ContainerCode, COUNT(indexdata.indexid) AS TotalFiles, subco.SubcoName, indexdata.indexdate
FROM containers INNER JOIN
indexdata ON containers.containerid = indexdata.containerid INNER JOIN
subco ON subco.subcoid = containers.subcoid
WHERE (containers.userid = 5)
GROUP BY containers.containercode, subco.SubcoName, indexdata.indexdate
ORDER BY indexdata.indexdate

Indexdata.indexdate is a long date, eg 08/08/2007 19:09:03 PM.
I need it to be 08/08/2007 - I know how to do that, but how do I get my Group By to show only the few valid results instead of each containers.containercode for each miinute/second in the date.

To explain - if I don't group by the date, I get about 5 containers returned in this query. All the containers are unique container codes. If I use the Group by Indexdata.indexdate, then I get a lot more results 'cause it shows the same container code over and over for the different hour:minute:second values in the date. I want to still only return the 5 unique containers, and the date eg 08/08/2007.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-08 : 08:44:05

USE CONVERT(VARCHAR(10), indexdata.indexdate, 121) in the select and the group by
instead of just indexdata.indexdate

Duane.