BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

   
  Oracle Tips by Burleson

Date Functions

Dates are stored in the database as a number that contains both the calendar data information and the time information.  We already discussed date math, where the unit of measure is one day.  Date functions allow you to modify and compare date data types.  Dates can be tricky.  If you use SYSDATE to insert date columns in tables, you will not only get the date component but also the time component.   

If you want to see all the records from today, a query based on today’s date will not match any rows in the database.  Basically, the times components of the two dates will not match.  This section will explain how to work with date data types and the next section will explain converting characters to dates and back again.  When working with calendars, you also have the problem that all months do not have the same number of days in them.  If you have a date and want the same date in three months, it becomes problematic.

months_between (l,e)

This function returns the months between two dates.  If I wanted to know how many months an employee has worked for the company, I can use this function.  There is an emp_hire_date in the emp table.

SELECT
  MONTHS_BETWEEN(SYSDATE,EMP_DATE_OF_HIRE)
FROM
  emp;

MONTHS_BETWEEN(SYSDATE,EMP_DATE_OF_HIRE)
----------------------------------------                              58.7710805
                              70.7710805
                              34.7710805
                              46.7710805
                              34.7710805
                              82.7710805
                               106.77108
                               154.77108
                               178.77108
                               166.77108

10 rows selected. 

Notice that it returns the fraction of a month.  You could use truncor round to make the results more readable.

add_months (d,n)

The add_months function gives you the same day, n number of months away.  The n can be positive or negative.

SELECT
  SYSDATE,
  ADD_MONTHS(SYSDATE,1),
  ADD_MONTHS(SYSDATE,2),
  ADD_MONTHS(SYSDATE,3),
  ADD_MONTHS(SYSDATE,4),
  ADD_MONTHS(SYSDATE,5),
  ADD_MONTHS(SYSDATE,6)
FROM
  dual;

SYSDATE   ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-FEB-05 24-MAR-05 24-APR-05 24-MAY-05 24-JUN-05 24-JUL-05

SELECT
  SYSDATE,
  ADD_MONTHS(SYSDATE,-1),
  ADD_MONTHS(SYSDATE,-2),
  ADD_MONTHS(SYSDATE,-3),
  ADD_MONTHS(SYSDATE,-4),
  ADD_MONTHS(SYSDATE,-5),
  ADD_MONTHS(SYSDATE,-6)
FROM
  dual; 

SYSDATE   ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH ADD_MONTH
--------- --------- --------- --------- --------- --------- ---------
24-JAN-05 24-DEC-04 24-NOV-04 24-OCT-04 24-SEP-04 24-AUG-04 24-JUL-04

last_day (d)

The last_day function returns the last day of the month of the date d.  If you want to find the first day of the next month, simply add one to the last_day results.

SELECT
  SYSDATE,
  LAST_DAY(SYSDATE) EOM,
  LAST_DAY(SYSDATE)+1 FOM
FROM dual;

SYSDATE   EOM       FOM
--------- --------- ---------
24-JAN-05 31-JAN-05 01-FEB-05

next_day (d, day_of_week)

The next_day function returns the date of the day_of_week  after date d.  day_of_week can be the full name or abbreviation.  Below, we get the date for next Monday, next Friday, and the first Tuesday of next month. 

SELECT
  SYSDATE,
  NEXT_DAY(SYSDATE,'MONDAY') "Next Mon",
  NEXT_DAY(SYSDATE,'FRIDAY') "Next Fri",
  NEXT_DAY(LAST_DAY(SYSDATE)+1,'TUESDAY') "First Tue"
FROM dual;

SYSDATE   Next Mon  Next Fri  First Tue
--------- --------- --------- ---------
24-JAN-05 31-JAN-05 28-JAN-05 08-FEB-05

round (d, format)

We talked about the round function as a numeric function but it is also a date function.  The round function returns the date rounded to the format. 

SELECT
  SYSDATE,
  ROUND(SYSDATE,'MONTH') Month,
  ROUND(SYSDATE,'YEAR')  Year
FROM
  dual; 

SYSDATE   MONTH     YEAR
--------- --------- ---------
24-JAN-05 01-FEB-05 01-JAN-05

Notice that SYSDATE is past midmonth so the month was rounded to the next month.  We are not past midyear, however, so the year was rounded to the beginning of the current year.

trunc (d, format)

As with the numeric trunc, the date version simply truncates the date to the level specified in the format. 

SELECT
  SYSDATE,
  TRUNC(SYSDATE,'MONTH') Month,
  TRUNC(SYSDATE,'YEAR')  Year
FROM
  dual;

SYSDATE   MONTH     YEAR
--------- --------- ---------
24-JAN-05 01-JAN-05 01-JAN-05


The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm

   

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.