Home » SQL & PL/SQL » SQL & PL/SQL » last date of the year
last date of the year [message #667428] Wed, 27 December 2017 11:14 Go to next message
sshree12
Messages: 19
Registered: December 2017
Junior Member
(TRUNC (as_of_date, 'YEAR') - 1) this function is wrong it gives incorrect values for different days



select (TRUNC (to_date('01-APR-16','dd-mm-yyyy'), 'YEAR') - 1) from dual;

output:31-DEC-15

i need end of date of the year of the given date
Re: last date of the year [message #667430 is a reply to message #667428] Wed, 27 December 2017 12:40 Go to previous messageGo to next message
Littlefoot
Messages: 21331
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In a few steps, so that you could easier follow what's going on.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> with test as
  2    (select date '2016-04-01' col from dual union
  3     select trunc(sysdate) from dual
  4    )
  5  select col,
  6    trunc(col, 'yyyy') first_of_year,
  7    add_months(trunc(col, 'yyyy'), 12) add_12_months,
  8    add_months(trunc(col, 'yyyy'), 12) - 1 last_day_of_year
  9  from test;

COL        FIRST_OF_Y ADD_12_MON LAST_DAY_O
---------- ---------- ---------- ----------
01.04.2016 01.01.2016 01.01.2017 31.12.2016
27.12.2017 01.01.2017 01.01.2018 31.12.2017

SQL>
Re: last date of the year [message #667432 is a reply to message #667428] Wed, 27 December 2017 14:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
sshree12 wrote on Wed, 27 December 2017 12:14
(TRUNC (as_of_date, 'YEAR') - 1) this function is wrong
It works as expected. If you truncate date to year you get January first of current year. Subtract 1 day and you will get December 31 of the previous year. So you need to add 12 months, as you were shown or use:

TO_DATE(TO_CHAR(as_of_date,'YYYY"-12-31"'),'YYYY-MM-DD')

SY.
Re: last date of the year [message #667439 is a reply to message #667428] Thu, 28 December 2017 07:40 Go to previous message
joy_division
Messages: 4817
Registered: February 2005
Location: East Coast USA
Senior Member
sshree12 wrote on Wed, 27 December 2017 12:14
(TRUNC (as_of_date, 'YEAR') - 1) this function is wrong it gives incorrect values for different days



select (TRUNC (to_date('01-APR-16','dd-mm-yyyy'), 'YEAR') - 1) from dual;

output:31-DEC-15

i need end of date of the year of the given date
You still do not understand how to use DATEs or format masks.

This is what you get from your query:
SQL> select (TRUNC (to_date('01-APR-16','dd-mm-yyyy'), 'YEAR') - 1) from dual;

(TRUNC(TO_D
-----------
31-DEC-0015
Previous Topic: current date - preovious date calculations
Next Topic: mysql and oracle issue using dblink
Goto Forum:
  


Current Time: Thu Jan 18 14:35:40 CST 2018

Total time taken to generate the page: 0.06332 seconds