CODEWITHSUNDEEP
sqldatabaseoracle-database
JK1
JK1

Reputation: 33

Oracle : Convert DD-MM-YYYY to MON-YYYY

In the following code SDate ='10-04-2020' format is DD-MM-YYYY and I need only the month and the year to compare it with the given date which is MON-YYYY

SELECT WR.EmpID AS EMPSID, SUM(WR.ESALARY) AS TOTAL_SALARY
FROM WEEKLY_REPORT WR
WHERE WR.EmpID = 'E009' AND TO_CHAR(WR.SDate,'MON-YYYY') = TO_CHAR('APR-2020','MON-YYYY') 
GROUP BY WR.EmpID;

the error ORA-01722: invalid number

Upvotes: 0

Views: 1016

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Since your date is in dd-mm-yyyy format you need to use to_date(WR.SDate,'dd-mm-yyyy') before using to_char() on it. And instead of TO_CHAR('APR-2020','MON-YYYY') use 'APR-2020' which is a valid string.

SELECT WR.EmpID AS EMPSID, SUM(WR.ESALARY) AS TOTAL_SALARY
FROM WEEKLY_REPORT WR
WHERE WR.EmpID = 'E009' AND TO_CHAR(to_date(WR.SDate,'dd-mm-yyyy'),'MON-YYYY') = 'APR-2020' 
GROUP BY WR.EmpID;

DB-Fiddle:

Query to convert date to mon-yyyy:

 select TO_CHAR(to_date('10-04-2020','dd-mm-yyyy'),'MON-YYYY') result from dual

Output:

RESULT
APR-2020

Query to check the condition:

 select 'true' result  from dual
 where TO_CHAR(to_date('10-04-2020','dd-mm-yyyy'),'MON-YYYY') = 'APR-2020'

Output:

RESULT
true

db<>fiddle here

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

Is SDate a date? If so, it does not have a format. It doesn't make sense to pass a character string to to_char. 'APR-2020' is already a string.

Either compare a string to a string

TO_CHAR(WR.SDate,'MON-YYYY') = 'APR-2020'

Or compare a date to a date

trunc( wr.SDate, 'MM' ) = date '2020-04-01'

Upvotes: 1

Related Questions