Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle date

Tags:

oracle

How is Oracle date implemented? Is it stored as milliseconds or something like that?

like image 671
Igor Drincic Avatar asked Oct 09 '08 14:10

Igor Drincic


People also ask

What is the Oracle Date Format?

Oracle stores dates in an internal numeric format representing the century, year, month, day, hours, minutes, seconds. The default date format is DD-MON-YY.

How do I display a date in YYYY MM DD format in Oracle?

Use TO_CHAR to display it in any format you like. For example: SELECT TO_CHAR ( TO_DATE (date_value, 'yyyy-mm-dd') , 'mm/dd/yyyy' ) FROM table_x; Things are much easier if you store dates in DATE columns.

What is to date in Oracle?

TO_DATE(date_string, format) Converts a specified string to a date using the specified format mask. If the format mask is omitted the NLS_DATE_FORMAT value is used. SELECT TO_DATE('10/07/2004 13:31:45', 'DD/MM/YYYY HH24:MI:SS') FROM dual; TO_DATE('10/07/2004 ------------------- 10/07/2004 13:31:45 1 row selected.

What is current date Oracle?

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE. The following statement shows the current date in 'DD-MON-YYYY HH24:MI:SS' format : SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered.


1 Answers

An Oracle DATE stores the date and time to the second. An Oracle TIMESTAMP stores the date and time to up to 9 digits of subsecond precision, depending on the available hardware.

Both are implemented by storing the various components of the date and the time in a packed binary format. From the Oracle Concepts Guide section on dates

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

You can use the DUMP() function to see the internal representation of any particular date (or any other value for that matter), but that's probably more than you need (or want) to know.

like image 145
Justin Cave Avatar answered Sep 23 '22 10:09

Justin Cave