Detecting the INSERT format for DATE database fields?

Assume I got a database resp. table defintion with a DATE field. How do I fi
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:

>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.

>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)