OraFAQ Forum: SQL & PL/SQL » Convert Date formats

Home » SQL & PL/SQL » SQL & PL/SQL » Convert Date formats

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert Date formats [message #686969]

Wed, 08 February 2023 15:28

deahayes
Messages: 6
Registered: January 2023

Junior Member

Hi all,

I have two different date formats

date1: '2023/02/06 00:00:00'
date2: '25-JAN-23'
How can I convert either date in order to match the date formats using Dual.

I tried
SELECT TO_char('2023/02/06 00:00:00','DD-MON-YY HH24:MI:SS' ) FROM DUAL

but get an error
ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause: The specified number was invalid.
*Action: Specify a valid number.

Report message to a moderator

Re: Convert Date formats [message #686971 is a reply to message #686969]

Wed, 08 February 2023 17:00

Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA

Senior Member

It is unclear what you are trying to do, whether you are trying to convert a date to a character string in a specific format, which is what to_char is for, or you are trying to convert a character string in a specific format to a date, which is what to_date is for.

By default, dates are displayed according to the nls_date_format as demonstrated below.

SCOTT@orcl_12.1.0.2.0> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'
  2  /

Session altered.

SCOTT@orcl_12.1.0.2.0> SELECT SYSDATE FROM DUAL
  2  /

SYSDATE
-------------------
2023/02/08 14:46:26

1 row selected.

SCOTT@orcl_12.1.0.2.0> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY'
  2  /

Session altered.

SCOTT@orcl_12.1.0.2.0> SELECT SYSDATE FROM DUAL
  2  /

SYSDATE
---------
08-FEB-23

1 row selected.

SCOTT@orcl_12.1.0.2.0> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'
  2  /

Session altered.

SCOTT@orcl_12.1.0.2.0> SELECT SYSDATE FROM DUAL
  2  /

SYSDATE
--------------------
08-FEB-2023 14:46:27

1 row selected.

You can use to_char to display or convert a date to a character string in a different format, no matter what the nls_date_format is, as shown below.

SCOTT@orcl_12.1.0.2.0> SELECT TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') FROM DUAL
  2  /

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
08-FEB-2023 14:46:27

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL
  2  /

TO_CHAR(SYSDATE,'YY
-------------------
2023/02/08 14:46:27

1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT TO_CHAR (SYSDATE, 'DD-MON-YY') FROM DUAL
  2  /

TO_CHAR(SYSDATE,'D
------------------
08-FEB-23

1 row selected.

You can attempt to check the format using regexp_like to convert various strings in different formats to dates, as shown below and I suspect this is what you are trying to do. However, there are problems with this. For example, how is Oracle to tell if '2023/02/06' is February 6, 2023 or June 2, 2023? If you only have the two formats and you know what they are then this might work for you.

SCOTT@orcl_12.1.0.2.0> WITH tab AS
  2    (SELECT '2023/02/06 00:00:00' date_col
  3  	FROM   DUAL
  4  	UNION ALL
  5  	SELECT '25-JAN-23' date_col
  6  	FROM   DUAL)
  7  SELECT CASE WHEN REGEXP_LIKE (date_col, '^[0-9]{4}/[0-9]{2}/[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
  8  		   THEN TO_DATE (date_col, 'YYYY/MM/DD HH24:MI:SS')
  9  		 WHEN REGEXP_LIKE (date_col, '^[0-9]{2}-[A-Z]{3}-[0-9]{2}$')
 10  		   THEN TO_DATE (date_col, 'DD-MON-YY')
 11  		 ELSE
 12  		   NULL
 13  	    END date_col
 14  FROM   tab
 15  /

DATE_COL
--------------------
06-FEB-2023 00:00:00
25-JAN-2023 00:00:00

2 rows selected.

[Updated on: Wed, 08 February 2023 17:02]

Report message to a moderator

Re: Convert Date formats [message #686972 is a reply to message #686971]

Wed, 08 February 2023 18:49

deahayes
Messages: 6
Registered: January 2023

Junior Member

Thank you,
This helped me alot, to underStand date formats. TO clarify, date1 is a parameter coming from value selected in concurrent program. Date2 is a column in a table that is a date data_type.
I tried to create a query

Select date2 from mytable where TO_CHAR(date2, 'DD-MON-RR') <= TO_CHAR(date1, 'DD-MON-RR') but I get ORA-01722: invalid number

date1: '2023/02/06 00:00:00'
date2: '25-JAN-23'

Report message to a moderator

Re: Convert Date formats [message #686973 is a reply to message #686972]

Wed, 08 February 2023 20:27

Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA

Senior Member

You should be comparing dates as dates, not converting them to character strings and comparing those.

Assuming that you have the following table and data

SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
  2  /

Table created.

SCOTT@orcl_12.1.0.2.0> insert into mytable (date2) values (to_date ('25-JAN-23', 'DD-MON-YY'))
  2  /

1 row created.

SCOTT@orcl_12.1.0.2.0> commit
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> select * from mytable
  2  /

DATE2
---------------
Wed 25-Jan-2023

1 row selected.

If your date1 parameter is of date datatype, then you might have a procedure like the following and call it as below.

SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
  2    (date1 in date)
  3  as
  4  begin
  5    for r in
  6  	 (select date2 from mytable where date2 <= date1)
  7    loop
  8  	 dbms_output.put_line (r.date2);
  9    end loop;
 10  end test_proc;
 11  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> set serveroutput on
SCOTT@orcl_12.1.0.2.0> exec test_proc (to_date ('2023/02/06 00:00:00', 'yyyy/mm/dd hh24:mi:ss'))
Wed 25-Jan-2023

PL/SQL procedure successfully completed.

If your date1 parameter is varchar2 data type, then you might have a procedure like the following, using to_date within the procedure to convert the varchar2 data type to date type, and calling it as below.

SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
  2    (date1 in varchar2)
  3  as
  4  begin
  5    for r in
  6  	 (select date2 from mytable where date2 <= to_date (date1, 'yyyy/mm/dd hh24:mi:ss'))
  7    loop
  8  	 dbms_output.put_line (r.date2);
  9    end loop;
 10  end test_proc;
 11  /

Procedure created.

SCOTT@orcl_12.1.0.2.0> show errors
No errors.
SCOTT@orcl_12.1.0.2.0> set serveroutput on
SCOTT@orcl_12.1.0.2.0> exec test_proc ('2023/02/06 00:00:00')
Wed 25-Jan-2023

PL/SQL procedure successfully completed.

This is just a minimal example, since I don't know your process or how you run it. You would do something similar if you were returning a ref cursor or whatever.

[Updated on: Wed, 08 February 2023 20:32]

Report message to a moderator

Re: Convert Date formats [message #686974 is a reply to message #686973]

Wed, 08 February 2023 20:47

Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA

Senior Member

Probably the most confusing thing to newcomers is that DATE is a separate datatype and is not stored in any of the various formats that it can be displayed as when converted to character data. Therefore, there is no need for conversion to compare them. If you do use to_char, then you are comparing strings, not dates. That may work if your data is yyyy/mm/dd, but not in most situations.

If you order by dates or compare dates, you get correct results:

SCOTT@orcl_12.1.0.2.0> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SCOTT@orcl_12.1.0.2.0> select hiredate from emp order by hiredate
  2  /

HIREDATE
---------------
Wed 17-Dec-1980
Fri 20-Feb-1981
Sun 22-Feb-1981
Thu 02-Apr-1981
Fri 01-May-1981
Tue 09-Jun-1981
Tue 08-Sep-1981
Mon 28-Sep-1981
Tue 17-Nov-1981
Thu 03-Dec-1981
Thu 03-Dec-1981
Sat 23-Jan-1982
Thu 09-Dec-1982
Wed 12-Jan-1983

14 rows selected.

But, if you order by characters or compare characters, you do not get the same date order, because it is comparing the order of characters in the string, day first, then the alphabetic month, where DEC comes before JUN, then the year in this example.

SCOTT@orcl_12.1.0.2.0> select to_char (hiredate, 'DD-MON-YY') from emp order by to_char (hiredate, 'DD-MON-YY')
  2  /

TO_CHAR(HIREDATE,'
------------------
01-MAY-81
02-APR-81
03-DEC-81
03-DEC-81
08-SEP-81
09-DEC-82
09-JUN-81
12-JAN-83
17-DEC-80
17-NOV-81
20-FEB-81
22-FEB-81
23-JAN-82
28-SEP-81

[Updated on: Wed, 08 February 2023 20:50]

Report message to a moderator

Previous Topic:	XML parse
Next Topic:	Difference between two dates

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Oct 02 20:25:52 CDT 2023