select LastReqTime,
BlockedOn,
UncommitOps
from sa_conn_info()
WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0
We've recently begun seeing empty fields in the LastReqTime. Normal data example would be 2016-05-17 17:03:42.721
As one can see by my code, I'm checking for LastReqTime older than 60 minutes. If the field is null, the code errors. I need an IF or CASE or even a nested select statement that will allow me to check the LastReqTime for notnull, I would guess !='' OR perhaps a field length greater than 0. I was hoping for a valid date/time evaluation, but apparently it's quite a bit more complicated than that. ISDATE doesn't cut it. I've done quite a bit of coding before but SQL is a different animal since I need results fast I figured I'd ask the experts here (of course it would be here). Thanks in advance.
QUESTION ABOUT POSTING: The noob note that appears to the right of this says: "Please format your code. Just highlight it and press Control-K." The menu above has ctrl+k as insert bookmark. Am I missing something?
Have you tried isdate() with set dateformat dmy ??
set dateformat dmy
select
LastReqTime,
BlockedOn,
UncommitOps
from sa_conn_info()
WHERE
LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0
and (isdate(LastReqTime) = 1 or LastReqTime = '' or LastReqTime is null)
Thanks for the reply. I added your mods and ran it with no errors but at this point we don't have any null fields to verify.
I guess I'd like to rephrase my request to ensure we both agree it's the same goal. I want to be able to run the code, and return data if LastReqTime is older than 60 minutes, BlockedOn is 0 and UncommitOps is 0, without getting errors if LastReqTime is empty.
Thanks again.
select LastReqTime,
BlockedOn,
UncommitOps
from sa_conn_info()
WHERE
1 = CASE WHEN ISDATE(LastReqTime) = 0 THEN 0
WHEN LastReqTime < DATEADD(MINUTE,-60,GETDATE()) THEN 1
ELSE 0 END
AND BlockedON > 0 AND UncommitOps = 0
Thanks very much. I tweaked my module, adding your suggestions. No errors returned. Of course, at this point the null values are gone, so I cant really do a conclusive test, but I'll cross my fingers/eyes/toes, etc. The logic looks good. Thanks again!
No problem. What makes it work is that SQL always processes CASE statements in the exact order in which they are written. But, as we know, SQL can do normal WHERE conditions in any order it wants to.
Scott,
Thanks for the explanation on the logic. I have to confess, even though I have 20+ years of coding/programming/scripting, this is my first foray into SQL code. It seems as if the constructs are uniquely different from what I'm used to writing. At any rate, thanks again.
Thanks Kristen,
The reason I was confused was the note on the right as I was creating my very first post said to use ctrl+k to format text, but in the actual text editor ctrl+k is insert hyperlink. </> (as you pointed out), is preformatted text.
I can't say I've ever seen that Control-K message - I just tried a NEW post and didn't see anything. Any chance you could attach / upload an image of a screenshot pls? then I can ask Admin to fix it; its a bugbear of the regulars here that Newbies rarely figure out how to format their code, and if the instructions are wrong then it's hardly surprising!!
I tried a new post, but it didnt show, however, I do remember it said it would only show for the first couple posts, something like that. I'll create a dummy account and try it.
UPDATE: I created a test account and went to create a post. The initial message had taken up 1/2 of the page, and was on the right. With the new test account, the message about formatting didn't appear. Guess someone fixed it, so it's not an issue anymore.
