Home » SQL & PL/SQL » SQL & PL/SQL » How To display last day of a century (Oracle 11g)
How To display last day of a century [message #670163] Wed, 13 June 2018 00:00 Go to next message
narendraenamala
Messages: 8
Registered: November 2016
Junior Member
Hi All

How to display last day of a century by passing sysdate

result: 31-dec-2099

Regards
Naren

[Updated on: Wed, 13 June 2018 00:01]

Report message to a moderator

Re: How To display last day of a century [message #670164 is a reply to message #670163] Wed, 13 June 2018 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are many ways.
Here's 2 avenues to explore:
SQL> select to_char(sysdate,'CC') from dual;

TO
--
21

SQL> select trunc(sysdate,'CC') from dual;

TRUNC(SYSDA
-----------
01-JAN-2001
Note that the last day of the 21th century is 31-DEC-2100, NOT 2099.

[Updated on: Wed, 13 June 2018 00:42]

Report message to a moderator

Re: How To display last day of a century [message #670170 is a reply to message #670164] Wed, 13 June 2018 10:10 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Wed, 13 June 2018 01:42


Note that the last day of the 21th century is 31-DEC-2100, NOT 2099.

That is Gregorian calendar definition: 100 * n + 1 through 100 * (n + 1) when n=0,1,2,... "Common life" usage is 100 * n through 100 * n + 99. Oracle follows Gregorian calendar definition. Implementing "common life" definition:

SQL> select  add_months(trunc(sysdate,'cc'),12 * 99) - 1
  2    from  dual
  3  /

ADD_MONTHS(
-----------
31-dec-2099

SQL> select  to_date(to_char(sysdate,'cc') || '000101','yyyymmdd') - 1
  2    from  dual
  3  /

TO_DATE(TO_
-----------
31-dec-2099

SQL> 

SY.
Re: How To display last day of a century [message #670175 is a reply to message #670170] Wed, 13 June 2018 11:23 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Oracle follows Gregorian calendar definition. Implementing "common life" definition:

Smile

Previous Topic: USERENV, and OS_USER
Next Topic: oracle large query text
Goto Forum:
  


Current Time: Thu Mar 28 05:53:00 CDT 2024