Example: ISDATE Is Smarter Than It Looks

Question: If SQL Anywhere has ISNUMERIC() and ISDATE() functions, how come it doesn't have ISTIME() and ISTIMESTAMP()?

Answer: It does, they're both called ISDATE()!

Here's proof... Four tests involving strings containing good and bad DATE, TIME, TIMESTAMP and UTC TIMESTAMP literals:

BEGIN 
   DECLARE @good_date            VARCHAR ( 100 );
   DECLARE @good_time            VARCHAR ( 100 );
   DECLARE @good_timestamp       VARCHAR ( 100 );
   DECLARE @good_utc_timestamp   VARCHAR ( 100 );
   DECLARE @good_time2           VARCHAR ( 100 );
   DECLARE @bad_date             VARCHAR ( 100 );
   DECLARE @bad_time             VARCHAR ( 100 );
   DECLARE @bad_timestamp        VARCHAR ( 100 );
   DECLARE @bad_utc_timestamp    VARCHAR ( 100 );
   DECLARE @bad_time2            VARCHAR ( 100 );

   SET @good_date          = CAST ( CURRENT DATE AS VARCHAR ( 100 ) );
   SET @good_time          = CAST ( CURRENT TIME AS VARCHAR ( 100 ) );
   SET @good_timestamp     = CAST ( CURRENT TIMESTAMP AS VARCHAR ( 100 ) );
   SET @good_utc_timestamp = CAST ( CURRENT UTC TIMESTAMP AS VARCHAR ( 100 ) );
   SET @good_time2         = '1:1';

   SET @bad_date           = STRING ( @good_date, 'x' );
   SET @bad_time           = STRING ( @good_time, 'x' );
   SET @bad_timestamp      = STRING ( @good_timestamp, 'x' );
   SET @bad_utc_timestamp  = STRING ( @good_utc_timestamp, 'x' );
   SET @bad_time2          = STRING ( @good_time2, 'x' );

   SELECT '(1a)' AS "Test", @good_date AS "Value", ISDATE ( @good_date ) AS "ISDATE"  
   UNION
   SELECT '(1b)', @bad_date, ISDATE ( @bad_date ) 
   UNION
   SELECT '(2a)', @good_time, ISDATE ( @good_time ) 
   UNION
   SELECT '(2b)', @bad_time, ISDATE ( @bad_time ) 
   UNION
   SELECT '(3a)', @good_timestamp, ISDATE ( @good_timestamp ) 
   UNION
   SELECT '(3b)', @bad_timestamp, ISDATE ( @bad_timestamp ) 
   UNION
   SELECT '(4a)', @good_utc_timestamp, ISDATE ( @good_utc_timestamp ) 
   UNION
   SELECT '(4b)', @bad_utc_timestamp, ISDATE ( @bad_utc_timestamp ) 
   UNION
   SELECT '(5a)', @good_time2, ISDATE ( @good_time2 ) 
   UNION
   SELECT '(5b)', @bad_time2, ISDATE ( @bad_time2 ) 
   ORDER BY 1;

END;

Test     Value                           ISDATE
(1a)    '2012-02-14'                        1
(1b)    '2012-02-14x'                       0
(2a)    '15:26:48.592'                      1
(2b)    '15:26:48.592x'                     0
(3a)    '2012-02-14 15:26:48.592'           1
(3b)    '2012-02-14 15:26:48.592x'          0
(4a)    '2012-02-14 20:26:48.592+00:0'      1
(4b)    '2012-02-14 20:26:48.592+00:00x'    0
(5a)    '1:1'                               1
(5b)    '1:1x'                              0

Test 5(a) is especially interesting: the string '1:1' passes the ISVALID test... that's because it's not just a valid TIME but it's also a valid DATE, TIMESTAMP and UTC TIMESTAMP!

How can that be?

It's because in SQL Anywhere a valid DATE is also a valid TIME, and vice versa: a valid TIME is also valid DATE.

SELECT '3-2-1' AS "DATE", 
       CAST ( "DATE" AS DATE )      AS "AS DATE",
       CAST ( "DATE" AS TIME )      AS "AS TIME",
       CAST ( "DATE" AS TIMESTAMP ) AS "AS TIMESTAMP";

SELECT '2:1' AS "TIME", 
       CAST ( "TIME" AS DATE )      AS "AS DATE",
       CAST ( "TIME" AS TIME )      AS "AS TIME",
       CAST ( "TIME" AS TIMESTAMP ) AS "AS TIMESTAMP";

DATE,AS DATE,AS TIME,AS TIMESTAMP
'3-2-1','2003-02-01','00:00:00.000','2003-02-01 00:00:00.000'

TIME,AS DATE,AS TIME,AS TIMESTAMP
'2:1','2012-02-14','02:01:00.000','2012-02-14 02:01:00.000'

Curious, isn't it?

Not the part about a-valid-THIS-is-a-valid-THAT, but the part about "today" being used to fill in a missing date part, but not "now" for a missing time part: '00:00:00.000' is used instead.

---