CODEWITHSUNDEEP
oracle-databasedate
Sh4d0wsPlyr
Sh4d0wsPlyr

Reputation: 968

SQL Query with date that does not exist

I was recently working on some SQL queries where I had to separate the values into various months (e.g. December, January, Feb...) and make some comparisons. However I was at a loss for wondering about what to use for the ending day of each month. So I was wondering what happens when you define a date that does not technically exist. For example.

WHERE myDate BETWEEN '2016-01-01' AND '2016-02-31' //note Feb 31 does not exist.

My assumption (based on my current query seeming to return the proper results) is that it simply ignores the extra dates that do not exist (e.g. when counting the dates, it simply has no dates for the range outside of the regular dates).

Is this undefined behavior that I may run into trouble with in the future? Or is there a better way to do this to cover all basis?

Upvotes: 0

Views: 1068

Answers (1)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210912

Why don't you want to use LAST_DAY() function:

SELECT SYSDATE, trunc(LAST_DAY(SYSDATE)) last, 
   LAST_DAY(SYSDATE) - SYSDATE days_left FROM DUAL;

Output:

SYSDATE           LAST               DAYS_LEFT
----------------- ----------------- ----------
03.02.16 18:38:26 29.02.16 00:00:00         26

1 row selected.

Upvotes: 1

Related Questions