CODEWITHSUNDEEP
sqloracle-databasebetween
Abhijit jadhav
Abhijit jadhav

Reputation: 27

Unable to get data between two years

I am not getting data between two years, below is between condition

to_char(Wfc.APPLYDTM,'MM/DD/YYYY') between '12/11/2019' and '01/10/2020'

but I am getting data between '12/11/2019' and '12/31/2019' & '01/11/2020' and '01/01/2020' for these dates but not between two different years.

Please help

Upvotes: 0

Views: 67

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Do not convert date/time values to strings! Use the built in functionality.

Your logic is most simply expressed as:

Wfc.APPLYDTMbetween >= DATE '2019-12-11' AND
Wfc.APPLYDTMbetween < DATE '2020-01-11'

Note that the date constants are provided using the DATE keyword. This supposed ISO 8601 standard date formats (happily!).

Also note the use of >= and < rather than BETWEEN. The date data type in Oracle can include a time component -- even if you don't see it when you query the table. This ensures that all date/times are included in the range.

As an added benefit, this can use an index on (APPLYDTMbetween). Using a function usually precludes using an index, unless you have defined a function-based index.

Upvotes: 0

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14198

You should convert your data to Date to be able to compare correctly.

The main idea is you should compare date value instead of string value.

to_date(Wfc.APPLYDTM,'MM/dd/yyyy') between to_date('12/11/2019','MM/dd/yyyy') and to_date('01/10/2020','MM/dd/yyyy')

Read here to more details.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Try using TO_DATE instead of TO_CHAR, and then compare against valid Oracle date literals:

SELECT *
FROM Wfc
WHERE TO_DATE(APPLYDTM, 'MM/DD/YYYY') BETWEEN date '2019-12-11' AND date '2019-01-10';

Note that if APPLYDTM already be a date, then you don't need to call TO_DATE on it. It doesn't make sense to convert your data to character, if you intend to work with it as a date.

Upvotes: 3

Related Questions