Date display in Oracle/SQLDeveloper

    A post in OTN forum (forum question was, How can I tell SQL Developer to always display datetime values in full?) asked how SQLDeveloper will display "Hours & seconds" in a "DATE" datatype, when the data entered is Date with no time component. Will it error out or will it display '00:00:00"? To this, I was certain, SQLDeveloper would gracefully display the timestamp at midnight. However, to prove it, I have to key-in data in date column and display them.
Before we get to that, let us first understand how Oracle stores DATE datatype.
Oracle stores date in 7 bytes (Ref: Oracle Advanced Application Developer's Guide. to access click here) Quote: Oracle Database stores dates in its own internal format. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. End Quote
So let us get back to SQLDeveloper and analyze how it displays date datatype
I would use the seeded table EMPLOYEES which has "hire_date" column defined as DATE datatype for demonstration purposes.

Emp table Columns and Datatypes (Screen -01)

Let us take an internal dump of the date and display the data (Screen -02)

Data Display (Screen -02)

As you can see in the above screen, SQLDeveloper displays all Hire_date column data with the time component. How does it do it, when the data keyed in does not contain time component? Well, if the data does not contain time details, then Oracle stores it as 12:00:00 AM.
A brief note on the Datadump column:-
Let us take line # 6 and analyze. 120 represents Century, 109 represents Year (both are displayed as 100+Century/100+Year), then comes the month, and date. Next you would see 1,1,1 which represents the time portion (or in other words the time here is 12:00:00 AM). Time is stored as (HH+1),(MI+1),(SS+1) and displayed as 12+(HH-1) and so on. For example Line # 3 diplays 23-FEB-2010 06:34:12 AM and is stored as CC=(100+20), YY=(100+10), MM=2, DD=23, Hours=7 (6+1), Minutes=35(34+1), Seconds=13(12+1).


Why Century and Year are stored in excess 100 notation? so that we can represent BC numbers. If you look at Line # 4, you would notice that for Century and Year column the value is less that 100. That is because, the data represents BC.90-100=-10, the century inserted and the negative value will tell Oracle that it is a BC and not AD.Another point is that, we know that BC 1010 is greater than BC 1011 (line # 5). So it works  perfectly well in binary sorting too.

So, to conclude, there is absolutely no issues in displaying date by SQLDeveloper