-2

When querying an oracle date, I see only the date - not the time. Example

select D from ALIK_TZ

Result:

D
01-JUN-16

How can I see the time as well?

AlikElzin-kilaka
  • 34,335
  • 35
  • 194
  • 277
  • 1
    [Possible duplicate](http://stackoverflow.com/q/17223226/266304)... – Alex Poole Jun 01 '16 at 13:51
  • Just because TOAD/SQLDeveloper/Whatever is showing only the date-part doesn't mean much. When the data is returned to an application it's not a string, it's a binary DATETIME field. Your application should be formatting that datatype for display in whatever way fits your needs. It should not be done by the database. Otherwise, you have coupled your presentation-layer to your data-layer. – MatBailie Jun 01 '16 at 13:55

2 Answers2

0

Use TO_CHAR with time format. Example below.

Consider the date being saved in column named D, than the following query would get the time as well:

select to_char(D, 'DD-MON-YYYY HH24:MI:SS') D_TIME, D from ALIK_TZ;

Result:

D_TIME                  D
01-JUN-2016 06:14:04    01-JUN-16
AlikElzin-kilaka
  • 34,335
  • 35
  • 194
  • 277
  • Dates do not have a format - they are represented internally by [7 or 8 bytes](http://stackoverflow.com/a/13568348/1509264) and **always** have a time component. The display of dates is handled by the client program you are using to access the database and will either be formatted according to the settings in the client program or, if the client program gets this parameter from the database, by the Oracle's `NLS_DATE_FORMAT` session parameter. What this answer does is convert the date to a formatted string and does not return a date which the client can format. – MT0 Jun 01 '16 at 14:00
0

Since you have answered it yourself. Just to add the reason as why you are getting the result in the format DD-MON-YY format. Oracle docs says:

For input and output of dates, the standard Oracle date format is DD-MON-YY

Also note that Oracle uses the TO_CHAR function internally whenever a DATE value is displayed, Oracle will call TO_CHAR automatically using the default DATE format which is DD-MON-YY.

Hence you need to change it using the TO_CHAR function to get the date in the format which you want.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331