Ignoring Time stamp for a count in SQL Server 2005

Question

Hi all,

I am having an issue figuring out what I thought would be a simple query. I have a table with two fields (EntryDateTime, Message) that as you can guess logs specific messages with a time date stamp for a monitoring app.

I am looking to get a count of all messages of a type per day but the time part of the stamp (while needed for everything else) giving me issues here.

I was looking to do something like,

SELECT COUNT(Message)
FROM DBName.dbo.TableName
WHERE Message = 'LoginFailed'
GROUP BY EntryDateTime

What I am looking for as the output is something like

2009-06-26, 142

2009-06-27, 259

2009-06-28, 57

Of course this is giving me an output of messages more like

2009-06-26 00:01:01, 11

2009-06026 00:01:02, 12

Any help getting ride of the timestamp for this query would be very helpful. I would like to not have to manually enter any date ranges as this query will be searching a years worth of logging and I would love to not enter 365 date ranges for a BETWEEN type query.

Answer 1

What about something like this:

`
SELECT COUNT (Message), CONVERT(DATETIME, CONVERT(CHAR(10), EntryDateTime, 101))
FROM DBName.dbo.TableName
WHERE Message = 'LoginFailed'
GROUP BY CONVERT(DATETIME, CONVERT(CHAR(10), EntryDateTime, 101))

`

Answer 2

Try this:

SELECT DATEADD(dd, DATEDIFF(d, 0, Getdate()), 0)

Replace Getdate() with your Column Name.

You'll also want to look at this previous StackOverflow question and also this previous question