Working with Dates and Date Functions

CURRENT_DATE Function :

• _DATE is a date function that returns the current database server date and time.
  

You can use :

• CURRENT_DATE just as you would use any other column name.
• For example, you can display the current date by selecting CURRENT_DATE from a table.

Example :
Display the current date using the DUAL table.
» SELECT CURRENT_DATE;

Output
» date
» "2009-07-15"

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 dates
• Add hours to a date by dividing the number of hours by 24

Using Arithmetic Operators with Dates :

• SELECT ename, (CURRENT_DATE-hiredate)/7 AS WEEKS FROM emp WHERE deptno = 20;
• The above example displays the last name and the number of weeks employed for all employees in department 90. It subtracts the date on which the employee was hired from the current date (CURRENT_DATE) and divides the result by 7 to calculate the number of weeks that a worker has been
  employed.

Date Functions :
All date functions return a value of DATE data type:

• age(timestamp, timestamp) : Subtract arguments, producing a “symbolic” result that uses years and Months
• date_part(text, timestamp) : Get subfield (equivalent to extract);
• extract(field from timestamp) : The extract function retrieves sub fields such as year or hour from date/timevalues. source must be a value expression of type timestamp, time, or interval.
• date_trunc(’field’, source) : Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Using Date Functions :

Character Functions

Case-Manipulation Functions :

• These functions convert case for character strings

LOWER :

• Converts mixed-case or uppercase character strings to lowercase

UPPER :

• Converts mixed-case or lowercase character strings to uppercase

INITCAP :

• Converts the first letter of each word to uppercase and remaining letters to lowercase

SELECT 'The job id for '||UPPER(ename)||' is ‘ ||LOWER(job) AS "EMPLOYEE DETAILS“ FROM employees;

Using Case-Manipulation Functions :

• Display the employee number, name, and department number for employee Scott
• SELECT empno, ename, deptno FROM emp WHERE ename = ‘Scott';
  no rows selected
• SELECT empno, ename, deptno FROM emp WHERE INITCAP(ename) = ‘Scott’;
  

Character-Manipulation Functions :

• These functions manipulate character strings

Using the Character-Manipulation Functions :

SELECT empno, 'NAME IS: ‘||ename as NAME, job, LENGTH (ename), POSITION(‘A’ IN ename) AS "Contains 'a'?" FROM emp WHERE SUBSTR(job, 1,3) = 'MAN';

The slide example displays employee first names and a string joined together, the length of the employee name, and the numeric position of the letter a in the employee name for all employees who have the string MAN contained in the job ID starting at the first position of the job ID.