0

I am attempting to select a date column that includes multiple date formats, including yyyymmdd and yyyy.

When running the query below, the following error occurs: ORA-01840: input value not long enough for date format

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
select to_date(date_table.date,'yyyy-mm-dd'))
from head_table
inner join date_table
on date_table.recordID = head_table.recordID

Can someone please provide assistance on how to select a data based column that contains two formats.

My expected output for 4 different records would be:

2017-12-12
2014
2011-04-15
2015

Thank you!

ID10T
  • 25
  • 2
  • 11
  • 1
    what is your data column type? use `to_char` instead – Muhammad Muazzam May 31 '17 at 12:49
  • 1
    If your column is a date or timestamp, it has no format. Assuming it is a string, you need to find all the possible formats used in this column to represent a date, and build a query according to these formats. Can you list all the different formats in which dates are stored in this column? Also, your expected output only makes sense if compared to the corresponding starting data, so please post the data that should give that output. – Aleksej May 31 '17 at 12:52
  • Changing from to_date to to_char yields the ORA-01481: invalid number format model error. – ID10T May 31 '17 at 12:56
  • There are two formats of dates in this column, yyyymmdd and yyyy. My end results is to have the output display yyyy-mm-dd and yyyy. – ID10T May 31 '17 at 12:57
  • The table_date.date is varchar2(23). – ID10T May 31 '17 at 13:06
  • **Never** store date values in a `varchar` column. The problem you have right now is a direct result of that wrong design. You should change that column to a proper `DATE` (or `TIMESTAMP`) column as soon as possible. –  May 31 '17 at 14:04

0 Answers0