Split Date from Oracle Form to DB

Question

I have a table with columns(year,day-month) -date type- in my database. and a form with a text field for the user to enter a date. how can I split the entered date to save it on db as following

year     day_month
----     ---------
2018     03-04

I tried SUBSTR(TO_CHAR(TO_DATE(block.field)) in a trigger , but it didn't work bcz the column type is date, and I tried to add TO_DATE() as outer but the result was

year           day_month
----------     ----------
03-04-2018     03-04-2018

How can I do it without changing my columns type?

Answer 1

I'd suggest you NOT to do that. Always store DATE values into DATE datatype columns. ALWAYS.

Later, if you want to present them differently, apply appropriate functions (such as TO_CHAR) to those values and display them any way you want.

In your example, that would be

TO_CHAR(date_column, 'yyyy') year
or
EXTRACT (year from date_column) year

and

TO_CHAR(date_column, 'dd-mm') day_month

[EDIT]

Once again (to repeat what I've said in a comment): the fact that you named columns in the database "year" (whose datatype is DATE) and "day_month" (whose datatype is also DATE) is completely useless.

Right now is (dd.mm.yyyy hh24:mi) 03.04.2018 10:32.

DATE datatype contains both date and time, so - how do you plan to put "2018" into the "year" column? What will you do with its month/day/hour/minutes/seconds component? It can't just "vanish", has to have some value. Is it the first of January at 00:00:00? Or what?

The same goes to your "day_month" column - it'll contain year, as well as hours/minutes/seconds, whether you want it or not.

Let's start with the "year": if you want to extract it from the Form item, that would be TO_CHAR, such as

to_char(:block.some_item, 'yyyy')

which results in a string, '2018'. You can't store it into a DATE datatype column, so you have to apply TO_DATE to it:

to_date(to_char(:block.some_item, 'yyyy'), 'yyyy')

and it will result in 01.04.2018 00:00:00 >>> see? Day, month, hours ... everything is here.

The alternative is to create those columns as VARCHAR2, but that's even worse.

Seriously, don't do that.

Answer 2

Try the following and make the necessary changes in Oracle Forms, substitute block and columns names instead of variables.

DECLARE
   p_year   VARCHAR2 (8);
   p_date   VARCHAR2 (8);
BEGIN
   SELECT TO_CHAR (SYSDATE, 'YYYY') INTO p_year FROM DUAL;


   SELECT TO_CHAR (SYSDATE, 'DD-MM') INTO p_date FROM DUAL;

   DBMS_OUTPUT.put_line ('p_year --> ' || p_year || ' p_date --> ' || p_date);
END;

Answer 3

If your column is a DATE type, expect that it will require you to input a date data also.

In your case, you don't need to split a date. For the YEAR column, if the year value only matters to you, then you can use the TRUNC function

:BLK.YEAR_DATE_FIELD := TRUNC(:BLK.DATE_VALUE, 'YYYY');

and for the MONTH column, just save the date value there.

:BLK.MONTH_DATE_FIELD := :BLK.DATE_VALUE;

Also, maybe you just need to set the format mask of those two fields in Oracle forms. You can set the Format Mask of YEAR field to YYYY and MM-DD to the MONTH field.

Answer 4

The DATE data type is stored dates in tables as 7-bytes

byte 1 - century + 100
byte 2 - (year MOD 100 ) + 100
byte 3 - month
byte 4 - day
byte 5 - hour + 1
byte 6 - minute + 1
byte 7 - seconds+ 1

You CANNOT have a DATE data type that just stores year or month + day; it will always store all the components of the date/time.

So you either store the correct values in each column or you will have to make up values for the components you are not storing and will need to make sure that all the made up values are appropriate for the real values. It is just easier to use the real values in both columns.

So just do:

INSERT INTO your_table(
  year,
  day_month
) VALUES (
  :BLK1.T_DATE,
  :BLK1.T_DATE
);

Without splitting the date because a day_month without a year does not make sense (is 29th February a valid date? For the majority of years, no it isn't).

When you want to output it with a format then just format it as a string on output:

SELECT TO_CHAR( year, 'yyyy' ) AS year,
       TO_CHAR( day_month, 'dd-mm' ) AS day_month
FROM   your_table;