Re: Insert date and time to a table

Thanks for the reply!

Another quick question: how do I set my Oracle database to be case insensitive?

Right now if I do select * from mytable where username like 'a%', it'll return nothing as the initial letter is capital in the username table. I thought Oracle automatically uses case insentivitity. Where do I set this option?

Thanks!

Chris Hamilton wrote:

> 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
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Christopher Hamilton
> Oracle DBA -- Wall Street Sports
> chris_at_wallstreetsports.com
> http://www.wallstreetsports.com/
Received on Tue Jun 15 1999 - 20:31:21 CDT