Hi All
I am trying to insert data into a Sybase DB containing date columns, but can’t find the equivalent of what I would do for an Oracle DB to accomplish that. The key there to format the date is the function to_date, what is it for Sybase?
Oracle conform statement:
INSERT INTO table
(some_date, id, …)
VALUES (to_date(‘26.06.2006’,‘DD.MM.YYYY’), 1, …);
What is the default date format for iAnywhere? Is it dependant on the selected OS language?
I am on Mac OS X 10.4.
Best regards, Robert
You may want to look at the convert/dateformat functions as these can process a variety of date formats.
Alternatively to avoid using any functions, and use the date_order option.
set option date_order=‘dmy’;
…
insert into … VALUES (to_date(‘26.06.2006’,‘DD.MM.YYYY’), 1, …);
or
set option date_order=‘mdy’;
…
insert into … VALUES (to_date(‘06.26.2006’,‘DD.MM.YYYY’), 1, …);
Note that the default date_order option is ‘ymd’;
SQL Anywhere will implicitly convert to dates if you provide the proper order in the date format.
Hello Chris
Thanks for the hints!
chris.gruber:
You may want to look at the convert/dateformat functions as these can process a variety of date formats.
I tried various statements but I can’t get it to work for example:
insert into periods (is_current,fraction_name, CONVERT(DATE, from_date, 104),school_year,CONVERT(DATE, to_date, 104)) values (‘0’,‘HS’,‘1.8.2003’,‘2003’,‘31.1.2004’);
Do you mean by convert/dateformat something as the above stmt. And what’s wrong with that statement?
chris.gruber:
Alternatively to avoid using any functions, and use the date_order option.set option date_order=‘dmy’;
How to set this permanently (until I set it again) in Interactive SQL? Just as mentioned above?
Thanks and best regards, Robert
…
insert into … VALUES (to_date(‘26.06.2006’,‘DD.MM.YYYY’), 1, …);
or
set option date_order=‘mdy’;
…
insert into … VALUES (to_date(‘06.26.2006’,‘DD.MM.YYYY’), 1, …);
Note that the default date_order option is ‘ymd’;
SQL Anywhere will implicitly convert to dates if you provide the proper order in the date format.[/quote]
I think I mislead you on the convert; this function is more useful on data coming from the database server rather than entering the server.
You can set the date_order permanenantly with the following SQL statements.
set option public.date_order=‘mdy’;
set permanent;
Hi Chris
Thanks so far, unfortunatly, I am still not able to import my data.
chris.gruber:
I think I mislead you on the convert; this function is more useful on data coming from the database server rather than entering the server.
So you are saying that the CONVERT function can only be used with a SELECT statement?
chris.gruber:
You can set the date_order permanenantly with the following SQL statements.set option public.date_order=‘mdy’;
set permanent;
I did that and I assume it is ok, but when I import data like this:
is_current,fraction_name,from_date,school_year,to_date
“1”,“HS”,“1.8.2005”,“2005”,“31.1.2006”
“0”,“FS”,“1.2.2006”,“2006”,“31.7.2006”
it doesn’t work. I also found when using a scripted SQL INSERT INTO command, I need single quotes ('), whereas when importing via Servoy (menu import) I need double quotes ("). Could you explain what is the impact of single and double quotes in the iAnywhere database?
Thanks and best regards, Robert
I am not sure what Servoy is doing with respect to import functionality.
If you turn on request level logging, you can get a handle on what Servoy is doing. Add the following to the sybase.config file (at the top)
-zr sql
Perhaps Servoy import functionality expects the data be enclosed with double quote and then reworks the string.
When using SQL statements, the meaning of the double quotes indicates to the database server that the string is to be treated as an identifier (column-name,user, etc). When using files via some import facility and an external file, the import facility reads the file and has its own algorithm for handling double quotes so I cannot say what Servoy does.
What errors are occurring?
You may need to restart your server to get public options (date_order) set properly.
chris.gruber:
Alternatively to avoid using any functions, and use the date_order option.set option date_order=‘dmy’;
I am using “set option date_order=‘mdy’” and it is working perfectly [thank you]. However, when the date column is empty an error message appears saying “Cannot convert to a timestamp”, with an option to Continue or Stop. If I continue the record is skipped and the records containing dates are imported.
I would like the records without dates in the date column to also be imported. How can this be accomplished?
The import is being done by executing a bat file that contains:
dbisql -c "eng=contacts;dbn=contacts;uid=useridhere;pwd=passwordhere" -host localhost Expirations.sql
which calls the following Expirations.sql file:
set option date_order='mdy';
INPUT INTO "DBA"."policies"
FROM Expirations.csv
FORMAT ASCII
DELIMITED BY ','
(date_expires,policy_company,policy_description);
Dean Westover
Choices Software, Inc.