ORACLE LAB 5
Date Time Functions
SYSDATE
SYSTIMESTAMP
CURRENT_DATE
CURRENT_TIMESTAMP
sADD_MONTHS(d,n)
MONTHS_BETWEEN(d1,d2)
LAST_DAY (d)
NEXT_DAY(d)
ROUND(d[,format])
TRUNC (d[,format])
Working with Dates
• Oracle stores dates in an internal character
format : century, year, month, day, hours,
minutes, seconds.
• The default date format is DD-Mon-YY.
• SYSDATE is function returning date and time.
However when the function is executed it only
gives the date, to view the time part it needs to
be converted to character.
• DUAL is a dummy table used to view
SYSDATE.
Arithmetic with Dates
• Add or subtract a number to or from a
date for a resultant date value.
• Subtract two dates to find the number of
days between those days.
• Add hours to a date by dividing the
number of hours by 24.
Arithmetic with Dates
Operation Result Description
Date + number Date Adds a number of days to
a date
Date – number Date Subtracts a number of
days from a date
Date – date Number of days Subtracts one date from
another
Date + number/24 Date Adds a number of hours to
a date
Date functions
Function Description
Number of months between two
MONTHS_BETWEEN
dates
ADD_MONTHS Add calendar months to date
NEXT_DAY Next day of the date specified
LAST_DAY Last day of the month
ROUND Round date
TRUNC Truncate Date
Using Date Formats
• MONTHS_BETWEEN(’01-SEP-95’,’11-JAN-94’) 19.6774194
• ADD_MONTHS(’11-JAN-94’,6) ’11-JUL-94’
• NEXT_DAY(’01-SEP-95’,’FRIDAY’) ‘08-SEP-95’
• LAST_DAY(’01-SEP-95’) ’30-SEP-95’
Using Date Functions
• ROUND(’25-JUL-95’,’MONTH’) 01-AUG-95
• ROUND(’25-JUL-95’,’YEAR’) 01-JAN-96
• TRUNC(’25-JUL-95’,’MONTH’) 01-JUL-95
• TRUNC(’25-JUL-95’,’YEAR’) 01-JAN-95
Conversion Functions
Data type
Conversion
Implicit data type Explicit data type
conversion conversion
Explicit data type conversion
TO_NUMBER TO_DATE
NUMBER CHARACTER DATE
TO_CHAR TO_CHAR
TO_CHAR Function with dates
TO_CHAR(date, ‘fmt’)
The format model :
•Must be enclosed in single quotation marks and is case
sensitive.
• Can include any valid date format element.
• Has an fm (fill mode) element to remove padded blanks or
suppress leading zeros.
• Is separated from the date value by a comma.
Example:
SQL> SELECT first_name, email, TO_CHAR(HIRE_DATE,
‘fmMM/YY')AS Month_Hired FROM EMPLOYEES WHERE job_id =
'AD_VP';
Element of date format model
YYYY Full year in numbers
YEAR Year spelled out
MM Two-digit value for month
MONTH Full name of the month
DY Three-letter abbreviation of the day of the
week
DAY Full name of the day
Element Description
SCC or CC Century
Years in dates YYYY Year
YYY or YY or Y Last 3 / 2 / 1 digit of the year
Y,YYY Year with comma in this position
YEAR Year spelled out
BC or AD BC/AD indicator
B.C or A.D BC/AD Indicator with periods
Q quarter of the year
MM Month, two digit value
Element Description
MONTH Name of the month padded with
blanks to length of nine
characters
MON Name of the month, three letter
abbreviation.
RM Roman numeral month
WW or W Week of year or month
DDD or DD or D Day of year / month/ week
DAY Name of the day padded with
blanks to length of nine
character
DY Name of the Day; three letter
abbreviation
Element Description
AM or PM Meridian indicator
A.M or P.M Meridian indicator with periods
HH / HH12 / HH24 Hour of day or hour (1-12) or hour
(0-23)
MI Minute ( 0 –59 )
SS Second ( 0 – 59 )
SSSSS Seconds past midnight ( 0 –
86399)
TH Ordinal Number ( DDTH for 4TH )
SP Spelled out number (DDSP for
FOUR)
SPTH or THSP Spelled-out ordinal number
(DDSPTH – FOURTH)
Using TO_CHAR Function with dates
Example:
SELECT first_name, TO_CHAR(hire_date,'fmDdspth "of" Month
YYYY fmHH : MI : SS AM') AS HIREDATE FROM employees;
Sample Output:
ENAME HIREDATE
----------- ------------------------------------
KING Seventeenth of November 1981 12:00:00 AM
BLAKE First of May 1981 12 : 00 :00 AM
…….
