Re: Oracle date format

From: Turkbear <john.g_at_dot.spamfree.com>
Date: Wed, 10 Nov 2004 15:12:31 -0600
Message-ID: <6j05p0ls2j5f31tof6887451kuocmk3o88@4ax.com>

Bricklen <bricklen_at_zyahoo.zcomz> wrote:

>Dom wrote:
>> Bill wrote:
>>
>>> Hi,
>>> Thank you for everybody's comments.
>>>
>>> I am aware of the various Oracle functions available, e.g. TO_DATE.
>>> However, if I am NOT able to use those functions, how can I insert a time
>>> value into a column.
>>>
>>> I can do a "select sysdate from dual;" to find the date format I need to
>>> enter. How do I get the time format? or data and time format,
>>> combined in
>>> 1 string.
>>>
>>> Thanks
>>>
>>>
>>>
>>>
>>> "Bill Li" <bill_at_tadi.com> wrote in message
>>> news:cmdr02$pdk$1_at_news.sap-ag.de...
>>>
>>>> Hi all,
>>>> I am trying to insert date/time value into a column with datatype date.
>>>> During this insertion, I cannot use to_date or any other Oracle
>>>> functions.
>>>>
>>>> When I use the string, '01-jan-2004', the value is inserted okay.
>>>
>>>
>>> However,
>>>
>>>> when I included the time, e.g. '01-jan-2004 01:01:01', I get the error
>>>> ORA-01830 (date format picture ends before converting entire input
>>>
>>>
>>> string).
>>>
>>>> Does anyone know what is the native string value I can use to when
>>>> insert
>>>> into a date datatype?
>>>>
>>>> Thanks,
>>>> Bill
>>
>>
>> select to_char(sysdate,'mm/dd/yy hh:mi:ss') from dual;
>>
>> - Dom
>
>Which question is that answering? That is simply converting sysdate to
>_your_ specified format, which has absolutely nothing to do with the
>default format, which is part of what the OP was looking for.

To enter the date in the way you want to ( date and time without using any functions like to_date) you will need to change the default format for your session:

alter session set nls_date_format 'MM/DD/YYYY HH24:MI:SS'

So:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.2.1 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production

SQL> select sysdate from dual;

SYSDATE

---

10-NOV-04 SQL> alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE

---

---

11/10/2004 15:07:55

SQL> create table drop_me(
  2 df1 date);

 SQL> insert into drop_me values ('11/10/2004 15:07:55'); 1 row created.

SQL> select * from drop_me;

DF1

---

11/10/2004 15:07:55

  SQL> select df1 + 25 from drop_me;

DF1+25

---

12/05/2004 15:07:55 Received on Wed Nov 10 2004 - 15:12:31 CST