Topic
  • 5 replies
  • Latest Post - ‏2013-11-20T20:21:26Z by ShawnKFox
jeegarg
jeegarg
1 Post

Pinned topic isdate() function

‏2013-10-10T20:42:49Z | nzpda

Has anyone implemented  SQL server isdate() function in Netezza?

Updated on 2016-04-12T01:21:33Z at 2016-04-12T01:21:33Z by Tushar Patel
  • ShawnKFox
    ShawnKFox
    616 Posts

    Re: isdate() function

    ‏2013-10-14T23:19:24Z  

    There is an isdate() function in the /nz/extensions/nz/nzlua/examples directory if you install the Netezza Analytics package.  Might not be exactly what you want but it could be modified to meet your needs I'd expect.

  • david.darden
    david.darden
    4 Posts

    Re: isdate() function

    ‏2013-11-16T00:33:23Z  

    One thing to be aware of... this function works most of the time, but it is not 100%.  For example, the timestamp '2013-10-13 23:15:17 AM' (which is invalid) returns True from this IsDate logic, but will fail when you attempt to insert it in to Netezza.  Might not be an issue in your case, but it is something we've run in to.

  • ShawnKFox
    ShawnKFox
    616 Posts

    Re: isdate() function

    ‏2013-11-19T10:17:46Z  

    One thing to be aware of... this function works most of the time, but it is not 100%.  For example, the timestamp '2013-10-13 23:15:17 AM' (which is invalid) returns True from this IsDate logic, but will fail when you attempt to insert it in to Netezza.  Might not be an issue in your case, but it is something we've run in to.

    The only way you can get the Lua isdate function to return true for the date '2013-10-13 23:15:17 AM' is to provide an invalid date format.  So yes,  select isdate('2013-10-13 23:15:17 AM', 'YYYY-MM-DD HH24:MI:SS AM') will return true, but providing an hour mask of HH24 while also adding an AM/PM in the format string is wrong.  Garbage in, garbage out.  Either use HH24 without AM/PM in the format string or use HH with AM/PM.

  • jaegd
    jaegd
    1 Post

    Re: isdate() function

    ‏2013-11-20T10:13:21Z  
    • ShawnKFox
    • ‏2016-03-05T17:04:39Z

    The only way you can get the Lua isdate function to return true for the date '2013-10-13 23:15:17 AM' is to provide an invalid date format.  So yes,  select isdate('2013-10-13 23:15:17 AM', 'YYYY-MM-DD HH24:MI:SS AM') will return true, but providing an hour mask of HH24 while also adding an AM/PM in the format string is wrong.  Garbage in, garbage out.  Either use HH24 without AM/PM in the format string or use HH with AM/PM.

    Is 'YYYY-MM-DD HH:MI:SS A.M.' also an "garbage-in" date format string to lua, following your suggestion of using HH with AM/PM?
     
    For example, we've got data like '2013-11-19 10:14:22 A.M.',  a valid date as determined by to_timestamp ( i.e. select to_timestamp('2013-11-19 10:14:22 A.M.','YYYY-MM-DD HH:MI:SS A.M.') ) 
     
    However, using the above format string in lua results in a "to_date(): Failed to match entire string format string" error.
     
     
    select *
    from table(inza..nzlua('
    function processRow(input)
        success,tstamp = pcall(to_date,input,''YYYY-MM-DD HH:MI:SS A.M.'')
        if success then
            return { success, tstamp, tstamp }
        else
            return { success, tstamp, null }
        end
    end
     
    function getShape()
       columns={}
       columns[1] = { "success", boolean }
       columns[2] = { "tstamp str", varchar(100) }
       columns[3] = { "tstamp", timestamp }
       return columns
    end
    ','2013-11-19 10:14:22 A.M.'));  
  • ShawnKFox
    ShawnKFox
    616 Posts

    Re: isdate() function

    ‏2013-11-20T20:21:26Z  
    • jaegd
    • ‏2016-03-05T17:04:40Z
    Is 'YYYY-MM-DD HH:MI:SS A.M.' also an "garbage-in" date format string to lua, following your suggestion of using HH with AM/PM?
     
    For example, we've got data like '2013-11-19 10:14:22 A.M.',  a valid date as determined by to_timestamp ( i.e. select to_timestamp('2013-11-19 10:14:22 A.M.','YYYY-MM-DD HH:MI:SS A.M.') ) 
     
    However, using the above format string in lua results in a "to_date(): Failed to match entire string format string" error.
     
     
    select *
    from table(inza..nzlua('
    function processRow(input)
        success,tstamp = pcall(to_date,input,''YYYY-MM-DD HH:MI:SS A.M.'')
        if success then
            return { success, tstamp, tstamp }
        else
            return { success, tstamp, null }
        end
    end
     
    function getShape()
       columns={}
       columns[1] = { "success", boolean }
       columns[2] = { "tstamp str", varchar(100) }
       columns[3] = { "tstamp", timestamp }
       return columns
    end
    ','2013-11-19 10:14:22 A.M.'));  

    I'd call not correctly handing A.M. format a bug, unfortunately it is one that isn't very likely to be fixed.  The Lua adapter relies on the C library function strptime to parse time/date information and at least on Linux that function does not support using periods after the A and M.  So AM/PM works fine, but not A.M. or P.M.

    Wouldn't hurt to log an issue, but fixing it looks like it would be a lot of work for a small payoff.

    [edit] The documentation does suggest that A.M. format would work, which is also a documentation bug.  You can specify A.M. or P.M. in the format specification but when parsing the time information it will still only accept AM or PM.