CODEWITHSUNDEEP
sqloracle-database
DNac
DNac

Reputation: 2783

SQL +/- number meaning?

I could not find the meaning of the following SQL command:

where date between to_date('2013-03-01', 'yyyy-mm-dd') and trunc(sysdate, 'mm') -1

What does the "-1" mean / does?

The other example is

trunc(months_between(date1, date2))+1

I have searched for this, but could not find a thing.

Thank you for advice!

Upvotes: 1

Views: 791

Answers (3)

araknoid
araknoid

Reputation: 3125

+1 is way to add a day to the date

-1 is way to remove a day to the date

In your specific case:

the instruction trunc(sysdate, 'mm') -1 remove one month to the date, in this case is one month before the current date.

the instruction trunc(months_between(date1, date2))+1 compute the difference in month between the two dates and then adds one.

Give a look at this SQLFiddle

Upvotes: 2

Ed Gibbs
Ed Gibbs

Reputation: 26343

As others have answered, "date - 1" subtracts one day from the date. Here's more detail on your specific SQL snippets:

where date between to_date('2013-03-01', 'yyyy-mm-dd') and trunc(sysdate, 'mm') -1`

This evaluates to "date between 3/1/2013 and the end of last month"

  • TRUNC(some date, 'MM') chops the date to the beginning of the month
  • TRUNC(SYSDATE, 'MM') returns the beginning of the current month
  • TRUNC(SYSDATE, 'MM')-1 returns the last day of the previous month
trunc(months_between(date1, date2))+1

This is giving the number of full months between date1 and date2, treating any fraction of a month as a whole month. For example, if you gave it the dates 7/28/2013 and 7/29/2013 it would report one month, and it would also report one month if you gave it 7/1/2013 and 7/31/2013.

The MONTHS_BETWEEN function returns, as it implies, the number of months between two dates. The return value will have decimal places - for example a return value of 1.5 means one and a half months.

The TRUNC function, when applied against a numeric, will chop off all its decimals, so TRUNC(1.9999999) will return 1.

Upvotes: 2

t-clausen.dk
t-clausen.dk

Reputation: 44326

it is a lazy way of adding or subtracting day(s)

Upvotes: 1

Related Questions