0

Please don't confuse this as selecting month and year from an already stored date value. I need to store only month and year and retrieve it as. The column data type has to be DATE and not VARCHAR.

Sudha
  • 47
  • 1
  • 11
  • 1
    What's wrong with storing midnight on the first day of the montb? – Alex Poole Mar 31 '17 at 06:55
  • If you **must** use a `date` data type, perhaps you could use the convention that the day of the date value must always be some number, such as 1 and time of 00:00. - @AlexPoole you're too fast! – STLDev Mar 31 '17 at 06:56
  • Absolutely agree with the above comment of @Alex Pole! Also, if I may add - he can store any day of the month and the year, as long as he knows that this column will be used for that purpose. Then the "transformation", say `extract` month or year from it, may also be done in a virtual column which calculates all that. Even so - I don't see the point of storing only the month or the year in a `DATE` column (not `VARCHAR2`), other than redefining the actual `DATE` datatype or use a custom type or whatever .. – g00dy Mar 31 '17 at 07:00
  • The answer... is no.... it must be a date that you store in a date column. It can be the first of the month at to 00:00:00 as it is a vslid date. – Paul Maxwell Mar 31 '17 at 07:54
  • Thanks for the responses. I wanted to know if it is a possibility. Looks like the answer is no. – Sudha Apr 04 '17 at 12:37

2 Answers2

2

NO you can t store just month and year part of the date into a DATE column in oracle 11g

look this link

CompEng
  • 7,161
  • 16
  • 68
  • 122
  • 1
    True , A date is a "date", and date/month is just a representation of that "date", so for instance, yesterday is "date" that could be stored in a "date" column, and then retrieved the way you want (on a more technical point of view, dates are stored as seconds or milliseconds from year 1 or 0) – Gar Mar 31 '17 at 06:58
  • 1
    This is effectively a link-only answer. You would be better to quote the salient points of the linked article into your answer. – MT0 Mar 31 '17 at 08:36
1

A DATE data type is stored internally as 7- or 8-bytes and always has day, hour, minute and second components. So, if you are using a DATE data type then you will always have to store those values.

However, you can use TRUNC( date_value, 'MM' ) to truncate the date to the first day of the month and add this to a check constraint on your table to ensure that the day/time components are always a fixed value (01 00:00:00):

CREATE TABLE your_table (
  date_column DATE
              CONSTRAINT your_table__date_column__chk
                CHECK ( date_column = TRUNC( date_column, 'MM' ) )
);

INSERT INTO your_table ( date_column )
VALUES ( TRUNC( SYSDATE, 'MM' ) );
Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
  • @ErsinGülbahar a `DATE` will always have a day and time component - you just need to ignore them or ensure they are a fixed value (which can be done through a check constraint on the column). – MT0 Mar 31 '17 at 07:49
  • but the question is not that, – CompEng Mar 31 '17 at 08:14
  • 1
    @ErsinGülbahar the OP stated that "The column data type has to be `DATE`" - A `DATE` data type always has a day and time component so the only way to meet their requirement of using a `DATE` type is to either store the day/time and ignore it or to store a fixed value for day/time and which ever option is chosen then build a mechanism to ignore the unused components into the associated business logic. A date truncated to a fixed day/time value is simpler as it will allow date comparisons to treat values in the same month as equal. – MT0 Mar 31 '17 at 08:21