SAP SQL Anywhere

Question

So you have this table:

BEGIN
CREATE TABLE ATB
( ATBID INTEGER,
NAME VARCHAR (200),
SETUP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
PRIMARY KEY (ATBID));
INSERT INTO ATB (ATBID,NAME) VALUES (1,'NAME1');
INSERT INTO ATB (ATBID,NAME) VALUES (2,null);
INSERT INTO ATB (ATBID,NAME) VALUES (3,'NAME3');
END

From Sybase Central if you right-click the second of the rows and "Generate > Insert Statement," you get this statement:

INSERT INTO "Owner"."ATB" ("ATBID","NAME","SETUP") 
VALUES(2,NULL,'2010-11-04 10:13:45.805')

However, if you do this instead:

begin
declare @UL long varchar;
unload select * from ATB where ATBID = 2 into variable @UL;
select @UL;
end

You get this as the contents of the variable:

2,,2010-11-04 10:13:45.805

Why are there quotes around the timestamp in the insert statement and not on the unload select statement results? If the data is of a type that requires quotes I would think it would always require quotes.

Accepted Answer

The answer to this question is similar to the answer to your other question - the representation used in the unloaded text file is different from that which is used by SQL.

SQL is a complex language which needs to follow specific lexical parsing specifications and therefore strings such as timestamps need to be quoted so they are not confused with arithmetic expressions (e.g. '2010-11-04' is a string, 2010-11-04 is an expression equal to 1995).

In the case of unloaded text the rules are much simpler and have a different set of goals and requirements: columns are separated by commas, the domain of all column values are known (from the table specification) and column values only need to be quoted if they contain embedded characters which could confuse the parser. The overall goal is to reduce the size of the unloaded file.

SAP SQL Anywhere

Timestamp Values in Unload Select Statement Results

Follow this question

Related questions