How to make a date's month be capitalized while inserting data into a SQL table-CodePudding

I am inserting data into tables and have come across an issue. I am needing the date to be: "29-SEP-2000", but am getting "29-Sep-2000". Any help would be much appreciated!

INSERT INTO movies (title_id, title, description, rating, category, release_date)
VALUES
(title_id_seq.NEXTVAL, 'Being the Ricardos', 'Follows Lucy and Desi as they face a crisis that could end their careers and another that could end their marriage.', 'R', 'DRAMA', (TO_DATE('December 2, 2021', 'Mon DD, YYYY')));

CodePudding user response：

Wrong approach.

Dates aren't stored in "uppercase" nor "lowercase"; Oracle uses 7 bytes to store that info in its internal format. All you have to do is to insert a valid DATE datatype value:

SQL> CREATE TABLE movies
  2  (
  3     title_id       NUMBER,
  4     title          VARCHAR2 (30),
  5     description    VARCHAR2 (200),
  6     rating         VARCHAR2 (5),
  7     category       VARCHAR2 (20),
  8     release_date   DATE
  9  );

Table created.

SQL> CREATE SEQUENCE title_id_seq;

Sequence created.

Insert: note the release_date value - I used date literal which always looks like that: date keyword followed by value in yyyy-mm-dd format enclosed into single quotes.

SQL> INSERT INTO movies (title_id,
  2                      title,
  3                      description,
  4                      rating,
  5                      category,
  6                      release_date)
  7       VALUES (title_id_seq.NEXTVAL,
  8               'Being the Ricardos',
  9               'Follows Lucy and Desi ...',
 10               'R',
 11               'DRAMA',
 12               DATE '2021-12-02');

1 row created.

Date could've also been to_date function with appropriate format mask, e.g.

SQL> update movies set release_date = to_date('02.12.2021', 'dd.mm.yyyy');

1 row updated.

SQL> update movies set release_date = to_date('2021, Dec 02', 'yyyy, Mon dd', 'nls_date_language=english');

1 row updated.

All those values represent the same date: 2nd of December 2021.

It is up to you to present that value any way you want. How? By applying TO_CHAR function with desired format model.

By default, in my database (which means that yours might display it differently), it looks like this:

SQL> select release_date from movies;

RELEASE_
--------
02.12.21

The way you wanted it:

SQL> select to_char(release_date, 'MON dd, yyyy') release_date from movies;

RELEASE_DATE
------------
DEC 02, 2021

Error you posted in a comment (ORA-01722: invalid number) has nothing to do with dates; it is related to something different. Can't tell what; it is raised when you try to insert a character into a NUMBER datatype column. Are you sure you matched column names and appropriate data types in your INSERT statement? Because, everything works OK with my sample table.