CODEWITHSUNDEEP
sqloracle-databasedate
DarrylP
DarrylP

Reputation: 1

1st Day of Current Year in Date Range Criteria in PS Query

I know how to select the first day of the first month of the current year in a number of different formats. The following works fine: '01-JAN-' || TO_CHAR(TO_DATE(SYSDATE),'YYYY').

However, I need to use January 1, of the current year in a date range criteria in a YTD PSoft Query:

WHERE A.effdt BETWEEN (January 1, Current_Year) AND SYSDATE.

When I use the expression '01-JAN-' || TO_CHAR(TO_DATE(SYSDATE),'YYYY') in the criteria, I get the following error:

A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=1858, Message=ORA-01858:
a non-numeric character was found where a numeric was expected (50,380)`

Upvotes: 0

Views: 1320

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

You should NEVER compare LITERAL with DATE. Since, Oracle will do an IMPLICIT conversion. And, sooner or later, it would become a performance issue.

Explicitly convert the literal to date using TO_DATE.

For example,

Depending on the date value input method,

1. If you are passing the literal via some program

BETWEEN TO_DATE('01-01-2014','DD-MM-YYYY') and SYSDATE

2. If you already have the date value in table, then use TRUNC

BETWEEN TRUNC(SYSDATE, 'YYYY') and SYSDATE

Upvotes: 1

Related Questions