Home » SQL & PL/SQL » SQL & PL/SQL » Date Format - Y2k
Date Format - Y2k [message #304211] Tue, 04 March 2008 09:50 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I know how wrong this is:

The date in the database is in VARCHAR.

It shows up as DD-MON-YY

However I would like DD-MON-YYYY

I've tried using....
select to_date('31-DEC-05','DD-MON-RRRR') from dual


But that still displays just the YY. Any other tricks?
Re: Date Format - Y2k [message #304212 is a reply to message #304211] Tue, 04 March 2008 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Any other tricks?
TO_CHAR
Re: Date Format - Y2k [message #304214 is a reply to message #304212] Tue, 04 March 2008 09:58 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
Thanks!

select to_char(to_date('31-DEC-05','DD-MON-RR'),'DD-MON-YYYY') from dual


That worked and returned 31-DEC-2005.


But why?????

How is to_char able to know the century would be 20.. instead of 19...?
Re: Date Format - Y2k [message #304216 is a reply to message #304214] Tue, 04 March 2008 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database SQL Reference
Chapter 2 Basic Elements of Oracle SQL
Section Format Models
Subsection The RR Datetime Format Element

Regards
Michel
Re: Date Format - Y2k [message #304219 is a reply to message #304216] Tue, 04 March 2008 10:08 Go to previous message
hdogg
Messages: 93
Registered: March 2007
Member
An excerpt from the helpful link you posted from Oracle's site...

Quote:
*

If the specified two-digit year is 00 to 49, then
o

If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
o

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
*

If the specified two-digit year is 50 to 99, then
o

If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
o

If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.



That is ingenious!!! Awesome bandaid. No excuse for using crappy datatypes though.

[Updated on: Tue, 04 March 2008 10:08]

Report message to a moderator

Previous Topic: select query
Next Topic: Alter Table
Goto Forum:
  


Current Time: Sun Dec 04 02:14:52 CST 2016

Total time taken to generate the page: 0.07671 seconds