Danger! Domains!

Q: Why is the seconds portion of a smalldatetime column dropped when I insert a value to the database? According to the SQL Anywhere documentation, smalldatetime values are actually timestamps. Is this a bug, or intentional?

A: It's not a bug in SQL Anywhere itself, especially since SQL Anywhere does not drop seconds. You may be seeing a client-side or application program or interface issue.

Here's the story: There is no such thing as the "smalldatetime" data type in SQL Anywhere, in spite of the fact the Help calls it a "data type". There is something called the smalldatetime domain, and it is there for compatibility with Transact SQL.

If you are not seeing seconds, then one of two things is probably happening: Your code is truncating the value before storing it, or your code is truncating the value when retrieving it. By "your code" I mean anything that is not inside the database engine; it could be the interface software, the development tools, the class library you're using, whatever.

Something is pretending that smalldatetime is actually small, and it's not SQL Anywhere :)

Here is a test using SQL Anywhere 11.0.1 and dbisql. It shows that smalldatetime timestamps are stored with seconds, and that by default they are returned with seconds to three decimal places. It also shows you can use TIMESTAMP_FORMAT to change that to six decimal places:

CREATE TABLE t ( 
   pkey INTEGER NOT NULL PRIMARY KEY,
   now  SMALLDATETIME NOT NULL );

INSERT t VALUES ( 1, '2009-03-05 06:04:58.123456' );
COMMIT;

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123'

SET TEMPORARY OPTION TIMESTAMP_FORMAT = 'yyyy-mm-dd hh:nn:ss.ssssss';

SELECT * FROM t;

pkey,now
1,'2009-03-05 06:04:58.123456'

The important point is that SQL Anywhere stores what it receives.

The confusion surrounding domains (I hate 'em, never use 'em) is exactly the reason Foxhound shows the true data types:

-- DBA.t (table_id 704) in ddd11 - Mar 5 2009 6:17:58AM - Print - Foxhound © 2007 RisingRoad

CREATE TABLE DBA.t ( -- empty
   pkey   /* PK        */ INTEGER NOT NULL,
   now                    TIMESTAMP NOT NULL, -- domain smalldatetime
   CONSTRAINT ASA77 PRIMARY KEY (
      pkey )
 );

The smalldatetime domain comes buit-in to SQL Anywhere. You can create domains yourself, but when you do that, you are buying nothing good and a whole lot that is bad. Domains hide the true data types from the application programmer, leading to confusion and bugs because SQL works with the base data types, not domains.

Here's an example:

CREATE DOMAIN funky VARCHAR ( 5 ) NOT NULL DEFAULT '00000' 
                    CHECK ( LENGTH ( @funky ) = 5 );

CREATE TABLE t ( 
   pkey        INTEGER NOT NULL PRIMARY KEY,
   funky_data  funky );

INSERT t VALUES ( 1, '123' );

The INSERT gets rejected with the following message: Constraint 'ASA77' violated: Invalid value for column 'funky_data' in table 't'.

The definition of a domain can't be altered in any meaningful way, and it can't be dropped and recreated after it's been used for any column even if all the tables are empty. That means any benefit of abstraction is absent from domains.

It's better to bite the bullet and explicitly define all the attributes for each and every column in your database. That brings one extra benefit: You can specify constraint names that make runtime errors more meaningful.

For example, you can eliminate the useless 'ASA77' in the message above, replacing it with instructions telling the end users what they should do, not just what they did wrong:

CREATE TABLE t ( 
   pkey        INTEGER NOT NULL PRIMARY KEY,
   funky_data  VARCHAR ( 5 ) NOT NULL DEFAULT '00000' 
                  CONSTRAINT "Use exactly 5 characters" CHECK ( LENGTH ( @funky ) = 5 ));

INSERT t VALUES ( 1, '123' );

Now the INSERT gets rejected with this message: Constraint 'Use exactly 5 characters' violated: Invalid value for column 'funky_data' in table 't'

You can't specify the CONSTRAINT clause in a CREATE DOMAIN statement. You also can't use the new DROP DOMAIN IF EXISTS syntax. Both of those could be added to the syntax in the future, but IMO iAnywhere should not bother... domains are a bad idea in the first place.

I wish I could do DROP DOMAIN smalldatetime.

---

PS This is not an argument against having smalldatetime as a real data type. It's an argument against leading people to believe it exists when it doesn't.