Insert formatted date function using Oracle SQL*PLus?

Question

Am a Oracle PL/SQL newbie. Basically, I have a table created as follows:

CREATE TABLE MYTABLE (
    ID                 VARCHAR2(3 CHAR) NOT NULL PRIMARY KEY,
    LAST_UPDATE        DATE
);

Am trying to write a script which will run through SQL*Plus command:

insert into MYTABLE (
    id,
    last_update)
    values (
    sys_guid,
    --- Date call - what is placed here?
);

What can I do to insure that the date inserted is the correct date (at time of insertion) with this format (what function to call):

27-Oct-11

Will need this script to be executable from within SQL*Plus (read that there's numerous amounts of incompatibilities between ANSI SQL & PL/SQL running from SQL Developer vs. SQL*Plus).

Thanks for taking the time to read this!

score 4 · Accepted Answer · edited Oct 29 '11 at 16:16

4

Date fields don't have a format. You apply a format when you select them by using the to_char function. So what you want to do is

insert into mytable 
  (id, last_update) 
values
  (sys_guid, SYSDATE);

And you select from it using

select id, to_char(last_update, 'DD-Mon-YY')
from mytable;

edited Oct 29 '11 at 16:16

APC

144,005
19
170
281

answered Oct 29 '11 at 00:39

Paul Tomblin

179,021
58
319
408

@OMG, why do you keep removing my instruction on how to select the format he wants? – Paul Tomblin Oct 29 '11 at 00:43
I formatted first - you hadn't edited (or saved) - because SYSDATE doesn't need brackets. I have no idea what your edits have been about, but I don't see any value in selecting a date format when dealing with an INSERT -- the OP needs to know about TO_DATE more than TO_CHAR, and you don't even explain what SYSDATE is... – OMG Ponies Oct 29 '11 at 00:45
2

No he doesn't. He wants to insert the current date (so all he needs to know is sysdate or now()) and he wants the selected date to look like a specific format, so he needs to_char. Please stop making my answers worse. – Paul Tomblin Oct 29 '11 at 00:47
Ok, where does he ask how to *insert* dates that are formatted that way into the database? He never does. So why explain to_date? That's not what he's asking. He wants it look like it's in that format in the database. That's why I recommend he formats it that way when he selects from it using to_char. If you want to answer the question that wasn't asked, write your own answer, don't wreck mine. – Paul Tomblin Oct 29 '11 at 01:28
1

I answered the question that was asked. Nobody else has. I'm still not seeing the "constructive" part of anything you've done or said. – Paul Tomblin Oct 29 '11 at 01:45
What does participation matter, much less on a Friday night? You keep introducing things, rather than addressing with the issues raised. – OMG Ponies Oct 29 '11 at 02:06

Insert formatted date function using Oracle SQL*PLus?

1 Answers1