| last date of the year [message #667428] |
Wed, 27 December 2017 11:14  |
 |
sshree12
Messages: 20
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  |
 |
Littlefoot
Messages: 21782
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 |
Solomon Yakobson
Messages: 3244
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 |
joy_division
Messages: 4963
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
