How do i find the total number of records created on a given day using T-SQL?

Question

I need to find out the total number of records that were created on a given day.

e.g.

ID CreatedDate
1 17/07/2009
2 12/07/2009
3 17/07/2009
4 05/07/2009
5 12/07/2009
6 17/07/2009

Output:

3 Records were created on 17/07/2009
2 Records were created on 12/07/2009
1 Record was created on 05/07/2009

EDIT

Upon testing the second suggestion made by Chris Roberts of including the formatting in the SQL i get this error:

Syntax error converting the varchar value ' Records were created on ' to a column of data type int.

Does anyone know how to rework the solution so that it works?

Answer 1

select count(*), CreatedDate from table group by CreatedDate order by count(*) DESC

Answer 2

You should be able to get the data you're after with the following SQL...

SELECT COUNT(ID), CreatedDate FROM MyTable GROUP BY CreatedDate

Or - if you want to do the formatting in the SQL, too...

SELECT CONVERT(varchar, COUNT(ID)) + ' Records were created on ' + CONVERT(varchar, CreatedDate) FROM MyTable GROUP BY CreatedDate

Good luck!

Answer 3

Is the column actually a timestamp? In which case you will need to apply a function to remove the time component, e.g.:

SELECT COUNT(*), date(CreatedDate) FROM MyTable GROUP BY date(CreatedDate)

I don't know what the function is in T-SQL, it's date() in MySQL and trunc() in Oracle. You may even have to do a to_string and remove the end of the string and group by that if you lack this.

Hope this helps.