Ignoring Time stamp for a count in SQL Server 2005

Question

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.

To properly display code like T-SQL or C# or VB.NET, please highlight the lines in question and then use the "code" button (010 101) on the editor toolbar or press Ctrl-K on your keyboard. This will nicely format the code and provide syntax highlighting. — marc_s, Nov 18 '09 at 14:50

score 2 · Accepted Answer · edited Oct 15 '13 at 07:15

2

What about using 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))

edited Oct 15 '13 at 07:15

Aashray

2,753
16
22

answered Nov 18 '09 at 14:36

Jim B

8,344
10
49
77

Conversion to string isn't the fastest method. https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx – Aaron Bertrand Nov 18 '09 at 15:19
Very true. If this is going to be a huge table/result set, you'd want to try something different – Jim B Nov 18 '09 at 15:24
This worked great. There are a number of these messages in this table I am looking for, but it is in the 20-30K range and this was very fast. Thanks much – InsertOldUserIDHere Nov 19 '09 at 22:08

score 0 · Answer 2 · edited May 23 '17 at 11:45

0

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

edited May 23 '17 at 11:45

Community

1
1

answered Nov 18 '09 at 14:38

This one gave me the dates I needed but I was still having issues getting the count right. However, I have been looking into this and think I can use it else where. Thanks – InsertOldUserIDHere Nov 19 '09 at 21:52

Ignoring Time stamp for a count in SQL Server 2005

2 Answers2