CODEWITHSUNDEEP
sqldateoracle11g
Victor
Victor

Reputation: 17107

Get first and last date of a month given any date

I have a date in oracle in yyyy-mm-dd format. Is it possible to get from this the following:
1.The exact date 1 yr ago in yyyy-mm-dd format (so : 2013-02-01--> 2012-02-01 and 2013-02-28--> 2012-02-29)
2.The corresponding start date and end date of the same month in yyyy-mm-dd format

Upvotes: 0

Views: 326

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

Try this:

SELECT
  TRUNC(DATE '2013-02-01', 'MM') - INTERVAL '1' YEAR AS one_year_ago_first_day,
  LAST_DAY(TRUNC(DATE '2013-02-28', 'MM') - INTERVAL '1' YEAR) AS one_year_ago_last_day,
  TRUNC(DATE '2013-02-11', 'MM'),
  LAST_DAY(DATE '2013-02-11')
FROM
  dual;

Basically, you can get the first day of a month by using TRUNC with MM model format, so everything after month will be truncated (so day will be set to 1).

LAST_DAY - this one returns the date of the last day in the month of the date given as a parameter.

You can also use INTERVAL datatype and subtract it from given date.

Upvotes: 1

Related Questions