CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Harshana
Harshana

Reputation: 7647

oracle LAST_DAY(sysdate) comparison problem

Suppose i have a Date column which has the date 31-JUL-2011. Im getting the last date of the month using LAST_DAY(sysdate) which returns 31-JUL-11. But When i do a simple = comparison it does not give the result..i mean suppose tablename.lastdateofmonth = LAST_DAY(sysdate) it does not give the result but if i use tablename.lastdateofmonth = to_date('2011/07/31', 'yyyy/mm/dd') it gives the result.

Thanks.

Upvotes: 1

Views: 3041

Answers (3)

kutluk
kutluk

Reputation: 76

last day makes the current date as the last day of the month. however it keeps the time. you must trunc the values if you are going to compare them by just day-month-year. Truncing them will solve your problem.

tablename.lastdateofmonth = trunc(LAST_DAY(sysdate))

Upvotes: 5

Martin Schapendonk
Martin Schapendonk

Reputation: 13496

last_day() preserves the time component of sysdate. Use it together with trunc() like this:

select *
from table_name
where lastdateofmonth = last_day(trunc(sysdate));

Upvotes: 1

Chandu
Chandu

Reputation: 82913

LAST_DAY(sysdate) will return you the last date of the month including the time part.

Looks like the data in the column lastdateofmonth is just having date part and the imte part defaulted to 12:00 AM.

Try using this:

tablename.lastdateofmonth = TRUNC(LAST_DAY(sysdate))

Upvotes: 1

Related Questions