CODEWITHSUNDEEP
sqloracle-database
user2357036
user2357036

Reputation: 13

YYYYMMDD to YYYYMM in oracle

I have a column with DATE datatype in a table.

I am trying to retrieve the column values in YYYYMM format. My select query looks like below

select *
from tablename
where date column = to_char(to_date('12/31/4000','MM/DD/YYYY'),'YYYYMM');

I am getting below exception.

ORA-01847: day of month must be between 1 and last day of month

Appreciate any input on this.

Upvotes: 1

Views: 15182

Answers (5)

Krishna Prasad
Krishna Prasad

Reputation: 101

If your column (YYYYMMDD) is in number format, the simplest way to get YYYYMM would be

select floor(DATE/100)
from tablename;

Upvotes: 0

ngrashia
ngrashia

Reputation: 9884

If your date_column's data-type is DATE, then use

select *
from tablename
where TO_CHAR(date_column,'YYYYMM') = to_char (to_date('12/31/4000','MM/DD/YYYY'),'YYYYMM');

If your date_column's data-type is VARCHAR, then use:

select *
from tablename
where date_column = to_char (to_date('12/31/4000','MM/DD/YYYY'),'YYYYMM');

I somehow feel your error is because you have a space between date and column as "date column". If the field name in the table is "COLUMN", then just removing the word "DATE" from your original query would suffice, as:

select *
from tablename
where column = to_char(to_date('12/31/4000','MM/DD/YYYY'),'YYYYMM');

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I think the simplest method is:

where to_char(datecolumn, 'YYYYMM') = '400012'

Or, if you prefer:

where to_char(datecolumn, 'YYYYMM') = to_char(to_date('12/31/4000', 'MM/DD/YYYY'), 'YYYYMM');

Upvotes: 5

tbone
tbone

Reputation: 15473

Assuming that "date_column" is actually a date, and that you have an index on date_column, you can do something like this to return the data quickly (without truncating dates in all rows to do a comparison):

with dat as (
    select level as id, sysdate - (level*10) as date_column
    from dual
    connect by level <= 100
)
select id, date_column
from dat
where date_column between to_date('11/1/2013', 'MM/DD/YYYY') and last_day(to_date('11/2013 23:59:59', 'MM/YYYY HH24:MI:SS'))

Here I just dummy up some data with dates going back a few years. This example picks all rows that have a date in the month of November 2013.

Upvotes: 1

A B
A B

Reputation: 4148

Syntax-wise, the right hand date (to the right of the equals) is OK. But you are doing a character comparison, not a date comparison. This works for me in multiple databases:

select to_char (to_date('12/31/4000','MM/DD/YYYY'),'YYYYMM')
from dual;

Even though your column is named DATE_COLUMN, you are comparing based on characters in the query.

So, try this instead - this compares based on dates (NOT a character comparison) and truncates off the hour, minute, ETC. so you are only comparing the DAY:

select * from DATE_TAB 
where TRUNC(DATE1, 'DDD') = TRUNC(to_date('12/31/4000','MM/DD/YYYY'),'DDD');

NOTE: The DATE1 field above is a DATE field. If you're DATE_COLUMN is not a DATE field, you must convert it to a DATE datatype first (using TO_DATE, ETC.)

Upvotes: 1

Related Questions