SQL - take out the day+month

[SpookiePower]

I have a field called daReportCreated which holds
the date where the record is created. The date
format is in dansih like this -

31-12-2007 15:08:00

I would like to take out the day+month and then count
which day each year that have the most records.

I found this help -

SELECT DATE(CURRENT_TIMESTAMP); - returns a date (2004-06-22)

But i don't know how to use it.

 

[John Spencer]

Get the count of records for each day

SELECT DateValue(daReportCreated) as TheDate
, Count(daReportCreated) as TheCount
FROM YourTable
GROUP BY DateValue(daReportCreated)

Get the maximum Count per year
SELECT Year(TheDate) as theYear , Max(TheCount) as BigCount
FROM qOne
GROUP BY Year(TheDate)

Combine
SELECT TheDate, theCount
FROM qTwo INNER JOIN qOne
ON qTwo.BigCount = qOne.TheCount
and q.TwoTheYear = Year(qOne.TheDate)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SpookiePower]

Thanks. It works