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.

asked 19 Aug '12, 05:37

coolguy's gravatar image

coolguy
1111
accept rate: 0%


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"
*/
permanent link

answered 19 Aug '12, 17:40

Breck%20Carter's gravatar image

Breck Carter
26.9k437609883
accept rate: 21%

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.

permanent link

answered 19 Aug '12, 10:01

Justin%20Willey's gravatar image

Justin Willey
6.8k111144213
accept rate: 20%

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.

(19 Aug '12, 10:28) coolguy

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.

permanent link

answered 19 Aug '12, 13:39

Justin%20Willey's gravatar image

Justin Willey
6.8k111144213
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×103
×15
×13

question asked: 19 Aug '12, 05:37

question was seen: 5,496 times

last updated: 19 Aug '12, 17:40