Oracle date functions - interview questions & answers


Oracle date functions

<<Previous  Next>>
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.

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.

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.

Select current_date from employee Will return: 16-JAN-2010 10:14:33

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.

Select SYSTIMESTAMP from employee Will return: 16-JAN-10 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.

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.

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.

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.

to_char(sysdate, 'FMMonth DD, YYYY'); would return ‘Jan 10, 2010'
Here, FM parameter suppresses the blank spaces and zeros.

Also read
Oracle architecture

Describe Oracle architecture in brief, What is the function of SMON?, Explain different types of segment, Explain SGA memory structures..............

Oracle cursors interview questions

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.............

Oracle functions

What are conversion functions?, What is nested function?, What is NVL function?, What are SQL functions in oracle?............

Oracle Functions, Procedures, packages

Explain IN, OUT and INOUT in procedures, What are the rules of writing package?, Explain the rules for writing a package..............

Oracle numeric functions
Oracle object datatypes

Object data types are user defined data types. Both column and row can represent an object type................

Oracle table

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?............

<<Previous  Next>>

Write your comment - Share Knowledge and Experience


Interview questions
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us | We are hiring