Skip to Content
avatar image
Former Member

Evaluate date field, use only if not null/empty in query.

We have this query:

select LastReqTime,

BlockedOn,

UncommitOps

FROM sa_conn_info()

WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0

Normal data example would be 2016-05-17 17:03:42.721 in LastReqTime.

As one can see by my code, I'm checking for LastReqTime older than 60 minutes. If the field is null or empty, 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 Since I need results fast I figured I'd ask the experts here (of course it would be here). Thanks in advance.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 19, 2016 at 09:03 AM

    Hi John,

    I guess your problem is that things aren't the way we might intuitively expect them to be.

    While LastReqTime looks like a datetime, it technically is a varchar (255) as you'll see in the sa_conn_info reference. As a bonus, an empty LastReqTime is not null, but an empty string (blank string?). I can't currently retrieve a row with an empty value, in case of doubt check the length of a value that you have trouble with or retrieve the ASCII () function. Assuming it is an empty string, check for NULLIF (LastReqTime, '') instead of just LastReqTime. You might also use the ISDATE () function which indicates if the string value is a valid date and/or time.

    HTH

    Volker

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Volker Stöffler

      As usual, you've answered my question and taught me something at the same time. Believe it or not, I'm unused to actually having to ask for help. Usually Im the "GoTo" guy, but I'm new to SQL and I face a steep learning curve. Thanks again for everything!

  • May 19, 2016 at 04:16 AM

    Hi John,

    Unfortunately, I don't fully understand your issue.

    So I talk based on my guess.

    ISDATE function tests if a string argument can be converted to a date (/datetime).

    You can rewrite the query as follows.

    SELECT * FROM

    (select LastReqTime,

    BlockedOn,

    UncommitOps

    FROM sa_conn_info()

    WHERE ISDATE(LastReqTime) = 1

    )  SubQuery1

    WHERE LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0

    Please see the following document for more information about ISDATE function.

    http://dcx.sap.com/index.html#sqla170/en/html/81f9466d6ce21014894fd76b048c76ab.html

    Regards,

    Koichi

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Kiochi.

      Thank you for your response. I guess I should have explained a bit more when I posted.

      The main problem I had was that null values in LastReqTime were causing a "cannot convert"

      error when the query tried to see if the LastReqTime was older than 60 minutes. I need

      a way to have the query not process a record if LastReqTime is empty or null.

  • May 19, 2016 at 06:43 AM

    Hi John,

    I wonder if you could try add 'LastReqTime is not null', like this:

    select LastReqTime,

    BlockedOn,

    UncommitOps

    FROM sa_conn_info()

    WHERE LastReqTime is not null and LastReqTime < DATEADD(mi,-60,GETDATE()) AND BlockedON > 0 AND UncommitOps = 0

    Regards,

    Zhang Yun-zheng

    Add comment
    10|10000 characters needed characters exceeded