Hi, I want to create a database (tables and contents) via an SQL script. The user has to run the script in a German environment, but I don't know the standard formattings in his installation. I'ld prefer to use the standard DATE literals' format 'YYYY-MM-DD' (ISO 8601 / SQL-92) in the script:
insert into mytable (name, birthdate) values ('Juergen', '2010-12-31');
That fails if the user's date format is 'DD.MM.YYYY' (Germany). Which procedure do you prefer?
Using to_date
insert into mytable (name, birthdate) values ('Juergen', to_date('2010-12-31', 'YYYY-MM-DD'));
Change the current NLS_DATE_FORMAT and reset after running the script
/* fetch the current format and store it in a char variable named previous_format */
alter session set nls_date_format = 'YYYY-MM-DD';
/* next run the script using ISO 8601 formatted values */
/* reset the format to previous_format */
Or is there
any better procedure?
By the way, [NLS_DATE_FORMAT documentation|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams122.htm] says:
The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks. Double quotation marks indeed?
Thank you for helping! Juergen
PS. I myself don't use Oracle DB, but Firebird. I'm an author of a German Wikibook "Introduction to SQL" and want to describe correct context.