CONVERT NUMBER(10) FIELD IN ORACLE SQL TO DATE

I have a table where the date field is stored as a number (20161231 = 12/31/2016). I would like to convert this field to a date. I have tried the following, but have received error messages.

,TO_CHAR(TO_DATE(TR.CHEMO_DATE, 'YYYYMMDD'), 'mm/dd/yyyy') AS CHEM_DATE

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

I have tried several things to no avail. Thanks for your assistance

When you to a TO_DATE on a number, it implicitly casts the number to varchar2 and then passes that in to TO_DATE. As long as your number always had 8 digits (zero pads single digit months and days), and the date it represents actually exists on the calendar, it will work, though it is a bit sloppy to rely on implicit type conversions. It's better for clarity to be explicit about a TO_CHAR in a situation like this. But still, your code works.

So, you must have a bad numeric-date in your data. To find it, query:

SELECT * 
  FROM (SELECT TO_DATE(tr.chemo_date DEFAULT NULL ON CONVERSION ERROR,'YYYYMMDD') converted_date,
               tr.*
          FROM mytable tr)
 WHERE converted_date IS NULL
   AND chemo_date IS NOT NULL

You can also create a function that traps exceptions to accomplish the same thing:

CREATE OR REPLACE FUNCTION f_safe_int_to_date(in_char IN integer,in_format IN varchar2)
  RETURN date
AS
BEGIN
  var_date := TO_DATE(in_char,in_format);

  RETURN var_date;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;

/

 SELECT tr.*
   FROM mytable tr
  WHERE f_safe_int_to_date(tr.chemo_date,'YYYYMMDD') IS NULL
    AND tr.chemo_date IS NOT NULL

Once you identify the problem values you can probably figure out your data issue and resolve it.

If your column is a NUMBER then use TO_DATE to convert it to a date:

TO_DATE(TR.CHEMO_DATE, 'YYYYMMDD')

Note: it is unclear why you are using NUMBER(10) when the format only has 8 digits.

In Oracle, a DATE is a binary data-type that consists of 7 bytes representing century, year-of-century, month, day, hour, minute and second. It ALWAYS has those 7 components and it is NEVER stored in any particular format.

If you want to display a DATE in a specific format then either:

1. Change the settings of the client application you are using to access the database to allow it to format the date in a specific way.

   If you are using SQL*Plus or SQL Developer then you can use:

   ALTER SESSION SET NLS_DATE_FORMAT='mm/dd/yyyy';
   

   Then use the query above.

   If you are using another client application then consult the documentation for that application.

2. Convert the DATE to a string using TO_CHAR (but then it will not be a DATE):

   TO_CHAR(TO_DATE(TR.CHEMO_DATE, 'YYYYMMDD'), 'mm/dd/yyyy') AS CHEM_DATE
   

   (Exactly as you did in the question)