Monday, September 30, 2013

SQL Anywhere Half-Price Sale

There's a half-price sale going on in the SAP store... until December 31 almost all of the SQL Anywhere SKUs have been marked down by 50%:



Dilbert.com 2010-10-18


Friday, September 27, 2013

Product Suggestion: More ISQL SET Option Commands

ISQL already HAS most of what I was going to suggest: ISQL commands to change option settings on the fly, as part of a batch of commands being processed by ISQL.

Did you know that?


Here's an example:

SET OPTION TRUNCATION_LENGTH = 70;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 50;
SELECT PROPERTY ( 'RememberLastStatement' );
SET OPTION TRUNCATION_LENGTH = 30;
SELECT PROPERTY ( 'RememberLastStatement' );

PROPERTY('RememberLastStatement')                                      
---------------------------------------------------------------------- 
Yes                                                                    
(1 rows)


PROPERTY('RememberLastStatement')                  
-------------------------------------------------- 
Yes                                                
(1 rows)


PROPERTY('RememberLastStatemen 
------------------------------ 
Yes                            
(1 rows)

There's one option missing, however; there is no SET OPTION command to dynamically choose Tools - Options - SQL Anywhere - Results - Style - Text, you have to use the GUI:



Maybe a SET OPTION statement isn't appropriate here, maybe a dbisql command line option is better... it doesn't matter... what matters is the ability to change the option in a batch file without having to clickety-clack through the GUI whenever one or the other styles is more appropriate.


Wednesday, September 25, 2013

SAP TechEd 2013 Registration Fee Goes Up at 5:01 PM PDT This Friday

The registration fee for SAP TechEd 2013 in Las Vegas on October 21–25 goes up after this Friday: from US $2,695 to $2,795.





Monday, September 23, 2013

Latest SQL Anywhere Updates: 12.0.1.3958 for Mac OS

Current builds for the active platforms...

HP-UX     16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 Itanium  12.0.1.3894 (EBF 21788 SP60)       16 May 2013
          11.0.1.2958 (EBF 21793 SP96)       08 Apr 2013     End of Life 31 May 2014

IBM AIX   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
          12.0.1.3894 (EBF 21787 SP60)       16 May 2013
          11.0.1.2958 (EBF 21792 SP96)       08 Apr 2013     End of Life 31 May 2014

Linux     16.0.0.1642 (EBF 21801 SP4)        13 Sep 2013
          On Demand 1.0.4613 (SP3)           13 Sep 2013
          12.0.1.3873 Update                 05 Apr 2013
          12.0.1 Chinese,                    16 Apr 2013
                 Japanese Docs (Eclipse)     16 Apr 2013
          11.0.1.3027 (EBF 21786 SP98)       13 Sep 2013     End of Life 31 May 2014

Mac OS    16.0.0.1565 (EBF 21800 SP3)        13 Sep 2013
          12.0.1.3958 (EBF 21796 SP64)   *** 19 Sep 2013 ***
          11.0.1.2449 Update                 29 Jun 2010     End of Life 31 May 2014

Solaris   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 SPARC    12.0.1.3894 (EBF 21789 SP60)       16 May 2013
          11.0.1.2958 (EBF 21794 SP96)       08 Apr 2013     End of Life 31 May 2014

Solaris   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 x64      12.0.1.3894 (EBF 21790 SP60)       16 May 2013
          11.0.1.2958 (EBF 21750 SP96)       08 Apr 2013     End of Life 31 May 2014

Windows   16.0.0.1644 (EBF 21802 SP5)        13 Sep 2013
          On Demand 1.0.4613 (SP3)           13 Sep 2013
          12.0.1.3942 (EBF 21795 SP62)       15 Aug 2013
          12.0.1 French,                     25 Sep 2012
                 English,                    25 Sep 2012
                 German,                     25 Sep 2012
                 Chinese,                    28 Mar 2013
                 Japanese Docs (HTML/PDF)    28 Mar 2013
          11.0.1.2960 (EBF 21751 SP97)       16 Apr 2013     End of Life 31 May 2014 

Other Stuff...

 Older Updates

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.

  • The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.

Friday, September 20, 2013

Force Balance a + b + c = 100

The requirement is to display three integer percentage values a, b and c, where a + b + c always equals 100.

The values a and b come from table t, and c is calculated as the remainder 100 - a - b.

The problem is, the values of a and b may be too large, causing a + b to exceed 100. In this case the values of a and/or b must be lowered so that a + b = 100 (thus making c = 0).

Here are nine combinations of a and b, where 4 combinations are OK and five combinations exhibit the problem:

CREATE TABLE t (
   pkey  INTEGER PRIMARY KEY,
   a     INTEGER,
   b     INTEGER );

INSERT t VALUES ( 1, 0,   0   );
INSERT t VALUES ( 2, 10,  10  );
INSERT t VALUES ( 3, 100, 0   );
INSERT t VALUES ( 4, 0,   100 );
INSERT t VALUES ( 5, 55,  55  );
INSERT t VALUES ( 6, 200, 0   );
INSERT t VALUES ( 7, 0,   200 );
INSERT t VALUES ( 8, 90,  70  );
INSERT t VALUES ( 9, 150, 50  );
COMMIT;

SELECT pkey, 
       a,
       b,
       100 - a - b AS c
  FROM t
 ORDER BY pkey;

       pkey           a           b           c 
----------- ----------- ----------- ----------- 
          1           0           0         100 
          2          10          10          80 
          3         100           0           0 
          4           0         100           0 
          5          55          55         -10 
          6         200           0        -100 
          7           0         200        -100 
          8          90          70         -60 
          9         150          50        -100 
The trick is, how should the new "lowered" a and/or b values be calculated?

What's your solution? (don't peek!)


This is a real-world problem taken from the development of a new feature in Foxhound Version 3: The "Busy Wait Idle %" column will show the relative amount of time each connection has spent doing work, waiting for resources, and sitting idle with nothing to do.

Busy + Wait + Idle must add up to 100 because, well, this is the real world, but the SQL Anywhere performance properties used to calculate Busy and Wait aren't always [cough] in step... sometimes Busy + Wait exceeds 100, hence the need to "pull them down" so Busy + Wait = 100.

One way is to recalculate a and b as percentages of ( a + b ):
SELECT pkey, 
       IF t.a + t.b <= 100
          THEN t.a
          ELSE CAST ( ( t.a * 100 ) / ( t.a + t.b ) AS SMALLINT )
       END IF AS a,
       IF t.a + t.b <= 100
          THEN t.b
          ELSE 100 - a 
       END IF AS b,
       100 - a - b AS c
  FROM t
 ORDER BY pkey;

       pkey           a           b           c 
----------- ----------- ----------- ----------- 
          1           0           0         100 
          2          10          10          80 
          3         100           0           0 
          4           0         100           0 
          5          50          50           0 
          6         100           0           0 
          7           0         100           0 
          8          56          44           0 
          9          75          25           0 
This process is sometimes called "force balance" where a column of rounded numbers are fiddled, er, adjusted so they add up to a known total (like 100%) rather than displaying some lame explanation about "rounding errors"... everyone knows that rounded numbers are inherently imprecise, so as long as the adjustment is made in a sensible manner it's sometimes better than displaying numbers that don't add up.

The force balance process often requires row-by-row processing; in the example shown here, it's nice to be able to embed the calculations in a query.


Monday, September 16, 2013

Latest SQL Anywhere Updates: V16 for Linux, Mac and Windows, 11 for Linux

Current builds for the active platforms...

HP-UX     16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 Itanium  12.0.1.3894 (EBF 21788 SP60)       16 May 2013
          11.0.1.2958 (EBF 21793 SP96)       08 Apr 2013     End of Life 31 May 2014

IBM AIX   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
          12.0.1.3894 (EBF 21787 SP60)       16 May 2013
          11.0.1.2958 (EBF 21792 SP96)       08 Apr 2013     End of Life 31 May 2014

Linux     16.0.0.1642 (EBF 21801 SP4)    *** 13 Sep 2013 ***
          On Demand 1.0.4613 (SP3)       *** 13 Sep 2013 ***
          12.0.1.3873 Update                 05 Apr 2013
          12.0.1 Chinese,                    16 Apr 2013
                 Japanese Docs (Eclipse)     16 Apr 2013
          11.0.1.3027 (EBF 21786 SP98)   *** 13 Sep 2013 *** End of Life 31 May 2014

Mac OS    16.0.0.1565 (EBF 21800 SP3)    *** 13 Sep 2013 ***
          12.0.1.3901 Update                 23 May 2013
          11.0.1.2449 Update                 29 Jun 2010     End of Life 31 May 2014

Solaris   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 SPARC    12.0.1.3894 (EBF 21789 SP60)       16 May 2013
          11.0.1.2958 (EBF 21794 SP96)       08 Apr 2013     End of Life 31 May 2014

Solaris   16.0 GA Dev Edition download       (date n/a)      Dev Edition registration
 x64      12.0.1.3894 Update                 16 May 2013
          11.0.1.2958 (EBF 21750 SP96)       08 Apr 2013     End of Life 31 May 2014

Windows   16.0.0.1644 (EBF 21802 SP5)    *** 13 Sep 2013 ***
          On Demand 1.0.4613 (SP3)       *** 13 Sep 2013 ***
          12.0.1.3942 (EBF 21795 SP62)       15 Aug 2013
          12.0.1 French,                     25 Sep 2012
                 English,                    25 Sep 2012
                 German,                     25 Sep 2012
                 Chinese,                    28 Mar 2013
                 Japanese Docs (HTML/PDF)    28 Mar 2013
          11.0.1.2960 (EBF 21751 SP97)       16 Apr 2013     End of Life 31 May 2014 

Other Stuff...

 Older Updates

 Free support! Q&A forum
   ...or, call Tech Support

 SQL Anywhere...
   ...Sybase home page 
   ...SAP home page 
   ...SAP Developer Center 

 Buy SQL Anywhere 

 Developer Edition... 
   [16.0] [12.0.1] [11.0.1]

 Download the...
   Educational Edition 
   Web Edition 

 Supported Platforms...
   SQL Anywhere 
   Linux 
   OnDemand

 ODBC Drivers for MobiLink

The asterisks "***" show which items have appeared on the Sybase website since the previous version of this page.
  • Only the latest fully-supported versions of SQL Anywhere (11.0.1, 12.0.1, 16.0 and On Demand) are shown here.

  • The "EBF 21788 SP60" numbers are the new SAP-specific codes associated with the build numbers "12.0.1.3894".

  • Just because an older version or different platform isn't "fully supported" any more doesn't mean you can't download files (or ask questions, or get help), it just means there won't be any more new Updates released.

Monday, September 9, 2013

Implementing YEARDIFF

Question: How do I compute the number of complete years between two timestamps?

Answer: Call DATEDIFF YEAR and if you don't like the answer, subtract 1!

That's not as silly as it sounds. DATEDIFF has two huge advantages over a start-from-scratch-and-do-it-yourself approach:

  • DATEDIFF takes care of those pesky leap years and the funky divisible-by-4-vs-100-vs-400 rule (2000 and 2400 are leap years, while 1800, 1900, 2100, 2200, 2300 and 2500 are NOT leap years), and

  • DATEDIFF works... it's already been tested by bazillions of developers and end users.
DATEDIFF's problem is that it computes the number of year boundaries between two timestamps, not the number of complete years. The two numbers are equal for exactly half of all possible pairs of timestamps, and DATEDIFF's answer is too high by 1 for the other half.

But wait!

There's another function you can call to determine if DATEDIFF got it right or not: DATEADD.

Yes, DATEADD... take the number of years returned by DATEDIFF and call DATEADD to add it to the first timestamp (assuming the first timestamp is the smaller one). If the result is less than or equal to the second timestamp, then DATEDIFF returned the right number. If not, then DATEADD's answer is too high by 1... hence the "subtract 1" in the answer above.

Here's the code, followed by a test using timestamps that are different by only one microsecond, as well as one year boundary:

CREATE FUNCTION YEARDIFF (
   IN @timestamp1   TIMESTAMP,
   IN @timestamp2   TIMESTAMP )
   RETURNS SMALLINT
   DETERMINISTIC
BEGIN

DECLARE @lesser_timestamp    TIMESTAMP;
DECLARE @greater_timestamp   TIMESTAMP;
DECLARE @sign                SMALLINT;
DECLARE @yeardiff            SMALLINT;

IF @timestamp1 IS NULL OR @timestamp1 IS NULL THEN
   RETURN NULL;
END IF;

IF @timestamp1 = @timestamp2 THEN
   RETURN 0;
END IF;

IF @timestamp1 < @timestamp2 THEN
   SET @sign = 1;
   SET @lesser_timestamp  = @timestamp1;
   SET @greater_timestamp = @timestamp2;
ELSE
   SET @sign = -1;
   SET @lesser_timestamp  = @timestamp2;
   SET @greater_timestamp = @timestamp1;
END IF;

SET @yeardiff = DATEDIFF ( YEAR, @lesser_timestamp, @greater_timestamp );

IF DATEADD ( YEAR, @yeardiff, @lesser_timestamp ) > @greater_timestamp THEN
   RETURN @sign * ( @yeardiff - 1 );
ELSE
   RETURN @sign * @yeardiff;
END IF;

END; 

SELECT DATEDIFF ( YEAR, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_1,
       YEARDIFF (       '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_1,
       DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_2,
       YEARDIFF (       '2012-01-01 00:00:00.0000000', '2011-12-31 23:59:59.9999999' ) AS YEARDIFF_2;

DATEDIFF_YEAR_1 YEARDIFF_1 DATEDIFF_YEAR_2 YEARDIFF_2 
--------------- ---------- --------------- ---------- 
              1          0              -1          0 

SELECT DATEDIFF ( YEAR, '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS DATEDIFF_YEAR_3,
       YEARDIFF (       '2010-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS YEARDIFF_3,
       DATEDIFF ( YEAR, '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS DATEDIFF_YEAR_4,
       YEARDIFF (       '2012-01-01 00:00:00.0000000', '2010-12-31 23:59:59.9999999' ) AS YEARDIFF_4;

DATEDIFF_YEAR_3 YEARDIFF_3 DATEDIFF_YEAR_4 YEARDIFF_4 
--------------- ---------- --------------- ---------- 
              2          1              -2         -1 

Here's another test that how a straightforward call to DATEDIFF flubs a simple age calculation but gets it right when YEARDIFF handles the call to DATEDIFF:

SELECT DATEDIFF ( YEAR, '2012-07-27', '2013-07-28' ) AS correct_age,
       DATEDIFF ( YEAR, '2012-07-29', '2013-07-28' ) AS incorrect_age;

correct_age incorrect_age 
----------- ------------- 
          1             1 

SELECT YEARDIFF ( '2012-07-27', '2013-07-28' ) AS correct_age_1,
       YEARDIFF ( '2012-07-29', '2013-07-28' ) AS correct_age_2;

correct_age_1 correct_age_2 
------------- ------------- 
            1             0 


Wednesday, September 4, 2013

Beware DATEDIFF Alternatives


Previously on . . . The story began with Documenting DATEDIFF and continued with three episodes about the use and abuse of DATEDIFF in this blog, in Foxhound and in the Help.

Now the story turns to seven other SQL Anywhere functions that can be used instead of DATEDIFF to compute the difference between two timestamps.

Here's a comparison of how these seven functions stack up against DATEDIFF when applied to the same two timestamp values that are exactly one microsecond apart:

SELECT 'DATEDIFF' AS "function",
       DATEDIFF ( year,        '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
       DATEDIFF ( month,       '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
       DATEDIFF ( day,         '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
       DATEDIFF ( week,        '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
       DATEDIFF ( hour,        '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
       DATEDIFF ( minute,      '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
       DATEDIFF ( second,      '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second
UNION ALL
SELECT 'YEARS, etc',
       YEARS   ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
       MONTHS  ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
       DAYS    ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
       WEEKS   ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
       HOURS   ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
       MINUTES ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
       SECONDS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second;

function   year  month  day  week  hour  minute  second
--------   ----  -----  ---  ----  ----  ------  ------
DATEDIFF      1      1    1     1     0       1       0
YEARS, etc    1      1    1     0     0       1       1

Here's what the Help has to say about these seven alternatives to DATEDIFF, and how the Help stacks up against testing:
What The Help Says What Testing Says
YEARS The value of YEARS is computed by counting the number of first days of the year between the two dates. True. YEARS returns the number of year boundaries between the two parameters, just like DATEDIFF YEAR.
MONTHS If you pass two TIMESTAMP values to the MONTHS function, the function returns the integer number of months between them. False. MONTHS returns the number of month boundaries between the two parameters, just like DATEDIFF MONTH.
DAYS If you pass two TIMESTAMP values to the DAYS function, the function returns the integer number of days between them. False. DAYS returns the number of day boundaries between the two parameters, just like DATEDIFF DAY.
WEEKS Given two dates (Syntax 2), the WEEKS function returns the number of weeks between them. The WEEKS function is similar to the DATEDIFF function, however the method used to calculate the number of weeks between two dates is not the same and can return a different result. The return value for WEEKS is determined by dividing the number of days between the two dates by seven, and then rounding down. However, DATEDIFF uses number of week boundaries in its computation. This can cause the values returned from the two functions to be different. For example, if the first date is a Friday and the second date is the following Monday, the WEEKS function returns a difference of 0, but the DATEDIFF function returns a difference of 1. While neither method is better than the other, you should consider the difference when choosing between WEEKS and DATEDIFF. True. This is the best Help of all these functions, DATEDIFF included... it gives a clear explanation of the difference between counting units (weeks, etc.) and counting boundaries (week changes, etc).
HOURS If you pass two TIMESTAMP values to the HOURS function, the function returns the integer number of hours between them. True. Both HOURS and DATEDIFF HOUR return the number of hours, not the number of hour boundaries.
MINUTES If you pass two TIMESTAMP values to the MINUTES function, the function returns the integer number of minutes between them. False. MINUTES returns the number of minute boundaries between the two parameters, just like DATEDIFF MINUTE.
SECONDS If you pass two TIMESTAMP values to the SECONDS function, the function returns the integer number of seconds between them as a SIGNED BIGINT value. False. SECONDS returns the number of second boundaries between the two parameters. This is different from DATEDIFF SECOND which returns the number of seconds rather than the number of boundaries.

So . . .

WEEKS makes a nice (and nicely documented) alternative to DATEDIFF WEEK, but the rest of them?




Monday, September 2, 2013

DATEDIFF Mistakes Case Study 3: The Help


Previously on . . . The story began with Documenting DATEDIFF and continued with two episodes about the use and abuse of DATEDIFF in this blog and in Foxhound.

Now the story turns to DATEDIFF examples published in the SQL Anywhere 16 Help.

The Scoring System

  • FAIL means the DATEDIFF usage is badly flawed; it shouldn't have been coded that way, and it should be fixed.

  • LUCKY means the DATEDIFF usage may be flawed but it doesn't matter given the data values involved.

  • OK means the DATEDIFF usage is OK given the data types involved.

The Scores


Example 1 is OK: begin_poll event
This example creates a push request for a Notifier named Notifier A. It uses a SQL statement that inserts rows into a table named PushRequest. Each row in this table represents a message to send to an address. The WHERE clause determines which push requests are inserted into the PushRequest table.

To use the ml_add_property system procedure with a SQL Anywhere consolidated database, run the following command:
ml_add_property(
    'SIS',
    'Notifier(Notifier A)',
    'begin_poll',
    'INSERT INTO PushRequest
        (gateway, mluser, subject, content)
        SELECT ''MyGateway'', DISTINCT mluser, ''sync'',
            stream_param
            FROM MLUserExtra, mluser_union, Dealer
            WHERE MLUserExtra.mluser = mluser_union.name
            AND (push_sync_status = ''waiting for request''
                OR datediff( hour, last_status_change, now() ) > 12 )
            AND ( mluser_union.publication_name is NULL
                OR mluser_union.publication_name =''FullSync'' ) 
            AND Dealer.last_modified > mluser_union.last_sync_time'
);
Let's ignore the obvious problems with this code (the "Syntax error near DISTINCT", the unknown nature of the tables in the FROM clause, and so on) and assume that "datediff( hour, last_status_change, now()" refers to a valid TIMESTAMP value in last_status_change.

Guess what? Assuming that "> 12" means "thirteen full hours or more" then DATEDIFF HOUR works OK because unlike other forms of DATEDIFF it returns the number of HOUR units between the two timestamps, not the number of HOUR boundaries.

Here's proof using two timestamps that have 13 hour boundaries between them but differ by an interval of only 12:00:00.000001 (which DATEDIFF HOUR truncates to 12):
BEGIN
   DECLARE @last_status_change TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' );
   DECLARE @now                TIMESTAMP DEFAULT ( '2013 09 02 13:00:00.000000' ); 
   SELECT DATEDIFF ( HOUR, @last_status_change, @now );
END;

DATEDIFF(hour,@last_status_change,@now)
12

Example 2 is a FAIL: Trigger conditions for events
Notify an administrator of a possible attempt to break into the database:
CREATE EVENT SecurityCheck
TYPE ConnectFailed
HANDLER
BEGIN
 DECLARE num_failures INT;
 DECLARE mins INT;
 INSERT INTO FailedConnections( log_time )
 VALUES ( CURRENT TIMESTAMP );

 SELECT COUNT( * ) INTO num_failures
 FROM FailedConnections
 WHERE log_time >= DATEADD( minute, -5,
  current timestamp );
 IF( num_failures >= 3 ) THEN
  SELECT DATEDIFF( minute, last_notification,
   current timestamp ) INTO mins
  FROM Notification;
  IF( mins > 30 ) THEN
   UPDATE Notification
   SET last_notification = current timestamp;
   CALL xp_sendmail( recipient='DBAdmin',
      subject='Security Check', "message"= 
       'over 3 failed connections in last 5 minutes' )
  END IF
 END IF
END;
Assuming Notification.last_notification is a TIMESTAMP column, what does "DATEDIFF( minute, last_notification, current timestamp )" return?

Answer: It returns the number of MINUTE boundaries between the two timestamp values. This usage must be marked as a FAIL because DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps.
BEGIN
   DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' );
   DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); 
   SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp );
END;

BEGIN
   DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' );
   DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000001' ); 
   SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp );
END;

DATEDIFF(minute,@last_notification,@current_timestamp)
31

DATEDIFF(minute,@last_notification,@current_timestamp)
30
DATEDIFF MINUTE can also return a smaller answer for a longer interval. Here's proof; the first DATEDIFF MINUTE call returns 31 for an interval of 00:30:00.000001 while the second returns 30 for an interval of 00:30:00.999999:
BEGIN
   DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 00:59:59.999999' );
   DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.000000' ); 
   SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp );
END;

BEGIN
   DECLARE @last_notification TIMESTAMP DEFAULT ( '2013 09 02 01:00:00.000000' );
   DECLARE @current_timestamp TIMESTAMP DEFAULT ( '2013 09 02 01:30:00.999999' ); 
   SELECT DATEDIFF ( MINUTE, @last_notification, @current_timestamp );
END;

DATEDIFF(minute,@last_notification,@current_timestamp)
31

DATEDIFF(minute,@last_notification,@current_timestamp)
30

Example 3 is OK: #hook_dict table
The following sample sp_hook_dbmlsync_delay procedure illustrates the use of in/out parameters in the #hook_dict table. The procedure allows synchronization only outside a scheduled down time of the MobiLink system between 18:00 and 19:00.
CREATE PROCEDURE sp_hook_dbmlsync_delay()
BEGIN
   DECLARE delay_val integer;
 SET delay_val=DATEDIFF(
   second, CURRENT TIME, '19:00');
 IF (delay_val>0 AND
     delay_val<3600)
 THEN
 UPDATE #hook_dict SET value=delay_val
   WHERE name='delay duration';
 END IF;
END
This usage scores OK for two reasons:
  • The code's more precise than the specs so an error doesn't matter; i.e., the specifications refer to hours "between 18:00 and 19:00" while the code calculates seconds "DATEDIFF( second, CURRENT TIME, '19:00')".

  • ...and besides, DATEDIFF SECOND returns the number of full seconds between two timestamps rather than the number of second boundaries; i.e., it's the other DATEDIFF rarity along with with DATEDIFF HOUR.

Example 4 is OK: sp_hook_dbmlsync_abort
The following procedure prevents synchronization during a scheduled maintenance hour between 19:00 and 20:00 each day.
CREATE PROCEDURE sp_hook_dbmlsync_abort()
BEGIN
  DECLARE down_time_start TIME;
  DECLARE is_down_time VARCHAR(128);
  SET down_time_start='19:00';
  IF datediff( hour,down_time_start,now(*) ) < 1
  THEN
    set is_down_time='true';
  ELSE
    SET is_down_time='false';
  END IF;
  UPDATE #hook_dict
  SET value = is_down_time
  WHERE name = 'abort synchronization'
END;
This one looks like a FAIL until you realize that down_time_start contains a constant value '19:00:00.000000'. That means it doesn't matter whether DATEDIFF HOUR is calculated using hours (it's not) or hour boundaries (it is), it's all the same.

In other words, this usage is OK.


Example 5 is OK: sa_performance_diagnostics system procedure
You can execute the following query to identify connections that have spent a long time waiting for database server requests to complete.
SELECT  Number, Name,
      CAST( DATEDIFF( second, LoginTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS T,
      IF T <> 0 THEN (ReqTimeActive / T) ELSE NULL ENDIF AS PercentActive
FROM  sa_performance_diagnostics()
WHERE T > 0 AND PercentActive > 10.0
ORDER BY PercentActive DESC;
This code's OK for the same reasons Example 3 is OK: For the connections of interest the difference between LoginTime and CURRENT TIMESTAMP grows very large very quickly compared with the size of any error introduced by DATEDIFF, and besides, DATEDIFF SECOND is calculated in seconds rather than second boundaries.
Example 6 is OK: sa_performance_diagnostics system procedure
Find all requests that are currently executing, and have been executing for more than 60 seconds:
SELECT  Number, Name,
      CAST( DATEDIFF( second, LastReqTime, CURRENT TIMESTAMP ) AS DOUBLE ) AS ReqTime
FROM  sa_performance_diagnostics()
WHERE ReqStatus <> 'IDLE' AND ReqTime > 60.0
ORDER BY ReqTime DESC;
Same thing again, same as Examples 3 and 5: This code is OK. A purist might use DATEDIFF MILLISECOND (Foxhound does) but with a predicate like "ReqTime > 60.0" it's not really necessary.
Example 7 is a FAIL: Text index refresh types
You can define your own strategy for refreshing MANUAL REFRESH text indexes. In the following example, all MANUAL REFRESH text indexes are refreshed using a refresh interval that is passed as an argument, and rules that are similar to those used for AUTO REFRESH text indexes.
CREATE PROCEDURE refresh_manual_text_indexes( 
   refresh_interval UNSIGNED INT )
BEGIN
 FOR lp1 AS c1 CURSOR FOR
   SELECT ts.*
   FROM SYS.SYSTEXTIDX ti JOIN sa_text_index_stats( ) ts
   ON ( ts.index_id = ti.index_id )
   WHERE ti.refresh_type = 1 -- manual refresh indexes only
 DO
   BEGIN
    IF last_refresh_utc IS null 
    OR cast(pending_length as float) / (
       IF doc_length=0 THEN NULL ELSE doc_length ENDIF) > 0.2
    OR DATEDIFF( MINUTE, CURRENT UTC TIMESTAMP, last_refresh_utc )
       > refresh_interval THEN
     EXECUTE IMMEDIATE 'REFRESH TEXT INDEX ' || text-index-name || ' ON "'
     || table-owner || '"."' || table-name || '"';
    END IF;
   END;
  END FOR;
END;
Let's ignore all the issues with this code and assume the DATEDIFF call was really written like this:
DATEDIFF( MINUTE, last_refresh, CURRENT TIMESTAMP ) > refresh_interval 
This code is a FAIL for the same reasons as the code in Example 2:
  • DATEDIFF MINUTE can return two different answers for exactly the same interval depending on the number of MINUTE boundaries between the two timestamps, and

  • DATEDIFF MINUTE can also return a smaller answer for a longer interval.
The error (less than one minute) might not be important for large values of refresh_interval, but for small values (like one minute) the behavior will be unpredictable.

The Final Score: 5 OK, 2 FAIL

Some might argue one or two OK scores were really just LUCKY, but it's the two FAILs that prove the point: DATEDIFF is difficult.