1

I am trying to convert the string date type that retrieved from a column. Below is the scenario

#input

date_str

02/08/24
21/09/2023
25.02.16
null
null

I need to convert above column into a new column with below format

formated_col
02-aug-24
21-sep-23
25-feb-16
null
null

'date_str' column is the part of cte table when I used below query, just to test the conversion, it works for eg:

with test_table as (select * from basetable)
select t.*, 
to_char(to_date(t.date_str, 'DD/MM/YYYY'), 'DD-MON-YY' ) as formatted_date
from test_table t

this create the expected output like this

formatted_date

02-aug-24
21-sep-23
25-feb-16
null
null

However when I am trying to create a table out of this, it

like

create table basetable as
with test_table as (select * from basetable)
select t.*, 
to_char(to_date(t.date_str, 'DD/MM/YYYY'), 'DD-MON-YY' ) as formatted_date
from test_table t

It throws below error

day of month must be between 1

i am not able to understand where exactly is the issue?

Improve this question
8
  • 4
    Dates are binary values, not strings. They have no format. Clean your data before loading into the database and only store it in date-typed columns. The input you posted is impossible to parse without making mistakes. And the output is completely useless - that's the Y2K bug all over again but worse. Is 21-sep-23 2021, 1923, 1021 or 2023? YOU DON'T KNOW. You may assume, but all are possible Commented Nov 26 at 9:46
  • 3
    date_str and the output are bugs, period. And if you assume this is extreme, Y2K quick-fix crick? 1920s come roaring back after mystery blip at UK's vehicle licensing agency, Y2K? How about Y2.02K as Lloyds suffers its second TITSUP* of the year, The Spanish family wrongly accused of child pornography due to a mistake reading a date Commented Nov 26 at 9:47
  • 4
    The issue? The choice to store dates in a character column is a serious issue. Use proper date data type, it will only accept valid dates, comparisons will work as expected etc. Commented Nov 26 at 9:59
  • 1
    @PanagiotisKanavos All of the strings match the DD/MM/YYYY format model (fiddle) as / will match other separators (unless you include the FX modifier, which the OP hasn't). None of the data provided in the question generates the OP's error. One issue with the OP's query is that 16 would be matched as the year 0016 and not 2016 - to solve that, the OP would want the YY (which also matches YYYY but not vice versa) or RR or RRRR (depending on whether the OP would want to convert 99 to 2099 using YY or 1999 using RR/RRRR). Commented Nov 26 at 10:58
  • 1
    @PanagiotisKanavos The OP states in the question that they are testing using the conversion format DD/MM/YYYY. Why are you assuming that the order of the date terms does not match the details of the question when the OP's sample data provides no evidence to the contrary? Yes, other date formats can be used but the OP has not indicated that in their sample data or their code. Commented Nov 26 at 11:08

2 Answers 2

Reset to default
1

Don't store dates as strings - store dates as DATEs. If you want a formatted string the you can either do that when you query the data or you can add a virtual column to the table.

When you parse your strings, if you have two-digit years then don't use YYYY as the format mask as 16 will get converted to the year 0016 and not 2016. You want to use RR or RRRR.

CREATE TABLE basetable (
  date_str VARCHAR2(10),
  dt       DATE,
  formatted_date VARCHAR2(9) GENERATED ALWAYS AS (
     CAST(TO_CHAR(dt, 'DD-MON-YY', 'NLS_DATE_LANGUAGE=English') AS VARCHAR2(9))
  )
);

Then you can insert the data using:

INSERT INTO basetable (date_str, dt)
SELECT date_str,
       TO_DATE(date_str DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/RR')
FROM   input_table;

Then the table will contain:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM basetable
DATE_STR DT FORMATTED_DATE
02/08/24 2024-08-02 00:00:00 02-AUG-24
21/09/2023 2023-09-21 00:00:00 21-SEP-23
25.02.16 2016-02-25 00:00:00 25-FEB-16
null null null
null null null

fiddle

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hello @MTO thanks for the answer Is there any alternative option to convert the string into expected date format as said, the table which have the date string are derived from a cte where column contains date along with string value. my objective was to first retirive the date part into date_str. rather inserting record after creating the table, is there any way to do with following cte if i can choose
1

The following part of your answer itself works for converting the date into the expected format without using an insert command.

I tried this way

CREATE TABLE basetable
AS
WITH test_table AS (
    SELECT *
    FROM basetable
)
SELECT
    t.*, date_str,
    TO_DATE(date_str DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/RR') AS formatted_date
FROM test_table t

This worked as expected

Improve this answer

1 Comment

To check where you have errors in your input data you can use SELECT * FROM basetable WHERE formatted_date IS NULL AND date_str IS NOT NULL; This should display the rows where date_str has an invalid format.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.