Sybase 11:issues exporting date, date-time and time formatted fields to text file

[Former Member]

Hi,

having issues trying to export dates, date-time fields and time formatted fields in order to import into another Database. This gives issues as when it exports to a textfile the date field isn´t encapsulated in double quotes as per a String Character.

Can anyone assist please? Thanks in advance.

[Breck_Carter]

Please tell us what version of SQL expects datetime values to be surrounded by "double quotes".

Please tell us EXACTLY what you want in the output file... proved an exact example.

The SQL Anywhere OUTPUT command has some options; for example:

CREATE TABLE t (
   pkey INTEGER NOT NULL PRIMARY KEY,
   dt   TIMESTAMP NOT NULL );

INSERT t VALUES ( 1, CURRENT TIMESTAMP );
COMMIT;

-- As Justin showed, FORMAT SQL produces a very funky result, only useful for SQL Anywhere isql...

SELECT * FROM t;
OUTPUT TO 'c:/temp/t.sql' FORMAT SQL;

/* 
SELECT * FROM t;
input format sql;
1,'2012-08-19 17:29:20.589'
END
*/

-- FORMAT TEXT uses 'single quotes' around datetimes by default...

SELECT * FROM t;
OUTPUT TO 'c:/temp/t.sql' FORMAT TEXT;

/*
1,'2012-08-19 17:29:20.589'
*/

-- The quote character can be changed...

SELECT * FROM t;
OUTPUT TO 'c:/temp/t.sql' FORMAT TEXT QUOTE '"';

/*
1,"2012-08-19 17:29:20.589"
*/

[justin_willey]

The dbisql OUTPUT statement with format SQL is a slightly odd beast that is only really compatible with the corresponding dbisql input statement which it generates itself. Look at this example:

create table blah(dt datetime, st char(20));
insert into blah values (current timestamp, 'hj');
commit;

then run the OUTPUT statement

select * from blah;
output to 'e:\\\\blah.sql' format sql;

the output file contains:

select * from blah;
input format sql;
'2012-08-19 18:25:00.484','hj'
END

create a new table blah2 in dbisql

create table blah(dt datetime, st char(20));

then paste the output file contents into dbisql and edit the select to refer to blah2 and run it

select * from blah2;
input format sql;
'2012-08-19 18:25:00.484','hj'
END

and it will load fine.

For general purpose exporting and importing you are probably better off using either OUTPUT & INPUT with the TEXT format or UNLOAD and LOAD which are database server SQL statements rather than dbisql commands.

[justin_willey]

Which tool are you using to do this exporting - if it's just from the data tab of a table view I don't think you have much control over this. In many csv definitions strings only need double quotes if the contain commas, CR, LF etc - dates shouldn't have commas so don't need quotes. (eg see RFC 4180)

If you use DBISQL and its OUTPUT statement you have much more control, including being able to force quotes.

[Former Member]

i'm using the output statement in dbisql as you say "SELECT * FROM TABLE1" where table1 contains several fields, of which 2 are formatted as date-time, when i run the following "SELECT * FROM TABLE1"; output to "C:table1table1.sql" "format sql";

the date time fields are not enclosed in double quotes, when i use insert to load the field back in, the isql doesn't recognise the hours in the time of the date time, enforcing me to encapsulate the datetime in double quotes.