Oracle - oracle date functions - Feb 18, 2010 at 15:20
PM by Rajmeet Ghai
Describe the use of following oracle date functions with an example.
Sysdate
Current_date
SYSTIME STAMP
ROUND and TRUNC in date calculation
TO_DATE and TO_CHAR formatting
1. Sysdate
Sydate in Oracle /PLSQL is used to return the current date and time of the
system in which the database is configured.
Example:-
Returns the System date and employee from the table
Select SYSDATE, id from employee Where emp_id >100;
2. Current_date
Current_date in Oracle /PLSQL is used to return the current date of the time
zone of the existing or running SQL session.
Example:
Select current_date from employee Will return: 16-JAN-2010 10:14:33
3. SYSTIMESTAMP
SYSTIMESTAMP in Oracle /PLSQL is used to return the current system (on which
the database is configured) date and time which includes fractions of seconds
and time zone.
Example:
Select SYSTIMESTAMP from employee Will return: 16-JAN-10 12.38.55.538741 PM
-08:00
4. ROUND and TRUNC in date calculation
ROUND in Oracle /PLSQL is used to return the next rounded value of a number.
The number of decimal places to be rounded is determined by a parameter.
Example:
ROUND(120.411) will return 120
ROUND(120.411, 1) will return 120.4
Select ROUND(salary_amt,2) from employee
TRUNC in Oracle /PLSQL is used to scrap or truncate the number of digits
specifed. The number of digits to be truncated is determined by a parameter.
Example:
TRUNC(120.411, 1) will return 120.41
Select TRUNC(salary_amt,2) from employee
5. TO_DATE and TO_CHAR formatting
TO_DATE function in Oracle /PLSQL is used to convert a given string in DATE
format.
Example:
to_date('2010/07/09', 'yyyy/mm/dd') would return a date value of Jan 10, 2010.
TO_CHAR function in Oracle /PLSQL is used to convert a given number (DATE or
number) to string.
Example:
to_char(sysdate, 'FMMonth DD, YYYY'); would return ‘Jan 10, 2010'
Here, FM parameter suppresses the blank spaces and zeros.
Also read
Describe Oracle architecture in brief, What is the function of SMON?, Explain
different types of segment, Explain SGA memory structures..............
What a SELECT FOR UPDATE cursor represent?, What WHERE CURRENT OF clause does in
a cursor?, Can you pass a parameter to a cursor?, Explain the functioning of
CURSOR FOR LOOP with example., Define Simple/Explicit , Parametric and
Internal/Implicit cursor.............
What are conversion functions?, What is nested function?, What is NVL function?,
What are SQL functions in oracle?............
Explain IN, OUT and INOUT in procedures, What are the rules of writing package?,
Explain the rules for writing a package..............
Object data types are user defined data types. Both column and row can represent
an object type................
Explain drop and truncate table command, Write the command to view the structure
of the table, What are the limitation of alter command?, Explain Alter Table
Command. What are the limitations of Alter Table command?............
|
