| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Insert date and time to a table
On 15 Jun 1999 13:38:04 PDT, Y2K <y2000_at_pacbell.net> wrote:
>I have a table (in Oracle 8.05) with a date column that stores date and
>time information. What's the best way to insert it so that when I do a
>select I'll have date (year, month, date) and time (hour, minute,
>second) and am/pm information? I've tried a couple of things with
>TO_DATE() function, but I was never able to get AM/PM information when I
>do a select. So I don't know if the info is lost or what. Here's one
>example of what I tried:
>
>ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI:SS AM";
>update mytable set creationdate=TO_DATE('06/14/1999 08:44:39
>PM','MM/DD/YYYY HH:MI:SS AM') where userid=0;
>select * from mytable;
Well, you're making it a little more complicated than it needs to be. Regardless of NLS_DATE_FORMAT, Oracle will automatically record the time and date in a date field.
You'll then have to set NLS_DATE_FORMAT appropriately to display the data properly, but it will be in there correctly nonetheless.
The easiest way is just thus:
SQL> update mytable set creationdate = sysdate;
or on an insert, just have the column defaulted
SQL> alter table mytable modify (creationdate default sysdate);
and it will auto populate that field with the current date/time unless you override it.
To get just the date (time of midnight), use trunc(sysdate) as your default or your insert value.
Chris
 |
 |