CODEWITHSUNDEEP
oracle-databaseplsqlsubqueryoracle-sqldeveloper
Lawrencxe
Lawrencxe

Reputation: 81

query to change the date format in the column

I have a report to create but there's a little problem I can't solve because the column(date) I generate has a different value. I use it in a subquery. My question is can I used a format so that I can manage to edit the value of the column? Please see the table below for reference,

My column(date) contains

date_columns
2019-06-20T11:09:15.674+00:00
2019-06-20T11:09:15.674+00:00
2019-06-20T11:09:15.674+00:00
2019-06-20T11:09:15.673+00:00

Now, my problem is it returned me ORA-01427: single-row subquery returns more than one row becaue of that 2019-06-20T11:09:15.673+00:00. Can I do a format to make it looked like 2019-06-20T11:09:15?

I tried the query below but nothing changed. It returned me a same error.

select distinct to_date(substr(dar.last_update_date,1,15),'YYYY-MM-DD HH:MI:SS')

select distinct to_date(dar.last_update_date,1,15,'YYYY-MM-DD HH:MI:SS')

Thanks!

Upvotes: 0

Views: 168

Answers (1)

APC
APC

Reputation: 146239

2019-06-20T11:09:15.673+00:00 appears to be a string of a datetime in the official XML representation. We can turn it into an actual timestamp using to_timestamp_tz() and then cast the timestamp to a date:

select cast(
          to_timestamp_tz('2019-06-20T11:09:15.673+00:00','YYYY-MM-DD"T"HH24:MI:SS:FFTZH:TZM') 
        as date)
from dual;

However, I'm not sure how this will resolve the ORA-01427: single-row subquery returns more than one row error. This exception occurs when we use a subquery like this … 

where empno = ( select empno 
                from emp
                where deptno = 30
                and sal > 2300 )

… and the subquery returns more than one row because the WHERE clause is too lax. The solution is to fix the subquery's WHERE clause so it returns only one row (or use distinct in the subquery's projection if that's not possible).

Upvotes: 2

Related Questions