Menu
I have 2 queries in SQL:
select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual; and
select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual; Both are giving me the same result. What is the difference between 'RRRR' and 'YYYY'?
231
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. SYSDATE is a function returning date and time. DUAL is a dummy table used to view SYSDATE.
TIMESTAMP is the same as DATE , except it has added fractional seconds precision. The biggest difference: DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds.
To set the date format: Select Preferences in the left frame, or select File, and then Preferences. Click the Planning icon, and then select Display Options. For Date Format, select MM-DD-YYYY, DD-MM-YYYY, YYYY-MM-DD, or Automatically Detect (to use your system's locale settings).
To_char formats a DATE into a string using the given format mask. To_date converts a STRING into a date using the format mask.
YYYY gives the current year as 4 digits.
RRRR format means 2-digit years in the range 00 to 49 are assumed to be in the current century (ie have the same first two digits as the current year), and years given as 50 through 99 are assumed to be in the previous century.
144
If the first 2 digits for the year are not specified in the date to be converted:
Try this sample code:
SELECT TO_DATE ('010199', 'MMDDYYYY') AS date_a, TO_DATE ('010199', 'MMDDYY') AS date_b, TO_DATE ('010199', 'MMDDRR') AS date_c, TO_DATE ('010199', 'MMDDRRRR') AS date_d FROM DUAL; The results when run on 12/01/2014:
DATE_A DATE_B DATE_C DATE_D --------- --------- --------- --------- 1/1/0099 1/1/2099 1/1/1999 1/1/1999 This oracle link gives a great description and examples.
From the above link:
- If the specified two-digit year is 00 to 49, then
- 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.
- 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
- 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.
- 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.
27
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
