Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

How to filter timstamps in sql.

Good morning/afternoon.

I am not the best at SQL, but I am trying to resolve an alert that isn't being triggered. I opened the database and I need to filter the dates that alerts have been triggerd.

I thought it would have been similar to a lookup function.

eg - timestamp > xx/xx/xxxx, < xx/xx/xxxx.

Again, I haven't really touched SQL. So my knowledge of a query is all but nothing. so I am trying to learn how it functions and how to generate commands on the back end.

I found a few and tried them (I suppose I should generate then again and show you the error messages)

date {

match => { "DATETIME" => [ "MMM d HH:mm:ss", "MMM dd HH:mm:ss", "ISO8601" ] }

target => "@timestamp"

add_tag => [ "tmatch" ]

}

SELECT *   FROM MyTable   WHERE [dateColumn] > '3/1/2009' AND [dateColumn] <= DATEADD(day,'3/31/2009',1)                  --make it inclusive for a datetime type     AND DATEPART(hh,[dateColumn]) >= 6 AND DATEPART(hh,[dateColumn]) <= 22                  -- gets the hour of the day from the datetime     AND DATEPART(dw,[dateColumn]) >= 3 AND DATEPART(dw,[dateColumn]) <= 5                  -- gets the day of the week from the datetime

select DATEPART(hh, Calltime)as MyHour, DATEPART(dd, Calltime)as MyDay, DATEPART(yy, calltime)as MyYear into Mytemp

from deployment

order by myyear, MyDay, MyHour

select *,COUNT(myhour)as MyCount from mytemp

group by myyear,myday,myhour

order by MyCount

Drop mytemp

If we take the last example - would I write.

where [timestamp] > '25/06/2017' AND [timestamp] <=dateadd (day, 30/06/2017', 1)

Msg 8116, Level 16, State 1, Line 1

Argument data type varchar is invalid for argument 2 of dateadd function.

Msg 8116, Level 16, State 1, Line 1

Argument data type varchar is invalid for argument 2 of dateadd function.

Find more posts tagged with

Accepted answers

All comments

mesverrum

So if I read this correctly you are looking for events in the last 7 days? Or do you need to be more granular like between specific hours on those days? The way you are doing it seems unnecessarily complex for someone starting out in SQL.

SQL is complaining because it doesn't recognize the string you fed it is supposed to be a datetime format so it can't do relative math operations against it, getting the format right for this can be a pain because it varies based on how your regional settings are and the way dates are displayed on screen can seem kind of misleading. You can try this and it may work:

where [timestamp] > cast('20170625' as date) AND [timestamp] <=cast('20170631' as date)

The way i normally do a lookup for recent dates would be like this

where datediff(day,[timestamp],getdate())< 7

That way I don't have to fuss with figuring out the time formatting and such

shaun_9999

TBH I haven't touched any real language since my college/uni days and that was PHP/C++ and other languages that my job never uses. But that was about a decade ago. So my best attempts at trying to even recall how to write a script and MS KB articles was the best I had. Ergo, why you thought it was so massively complex and I thought it was perfectly fine.

And it's all about expanding my knowledge and if I ever get issues like this, I want to at least attempt to resolve my issues and not raise a support call.

yaquaholic

First off all, the date format from your output example appears to be dd/mm/yyyy, where as your SQL code is using US style mm/dd/yyyy.

That might be a local thing, but worth checking.

Here is an working example (UTC time format)

SELECT * FROM AlertHistory

WHERE TimeStamp > '2017-06-02' -- this assumes '2017-06-02 00:00:00' or 00:00 2nd of June

AND TimeStamp < '2017-06-03 20:00:00' -- specifying eight o'clock on the 3rd of June 2017

I hope it helps

mesverrum

Yeah, I started learning SQL originally by picking apart reports and queries i found on Thwack and combining that with tutorials at SQL Introduction and stack exchange type searches