Date and Time Functions in SQL

We live in a civilization governed by times and dates, and most major implementations of SQL have functions to cope with these concepts.

Date and Time Functions #

This blog uses the table PROJECT to demonstrate the time and date functions.

SQL> SELECT * FROM PROJECT;

ADD_MONTHS #

This function adds a number of months to a specified date. For example, say something extraordinary happened, and the preceding project slipped to the right by two months. We could make a new schedule by typing

SQL> SELECT TASK, STARTDATE, ENDDATE ORIGINAL_END, ADD_MONTHS(ENDDATE,2)FROM
PROJECT;

Not that a slip like this is possible, but it’s nice to have a function that makes it so easy. ADD_MONTHS also works outside the SELECT clause

SQL> SELECT TASK TASKS_SHORTER_THAN_ONE_MONTH FROM PROJECT WHERE
ADD_MONTHS(STARTDATE,1) > ENDDATE;
TASKS_SHORTER_THAN_ONE_MONTH
----------------------------
KICKOFF MTG
TECH SURVEY
USER MTGS
DESIGN WIDGET

Analysis: ***### we will find that all the functions in this section work in more than one place. However, ADD MONTHS does not work with other data types like character or number without the help of functions TO_CHAR and TO_DATE, which are discussed later today.

LAST_DAY #

LAST_DAY returns the last day of a specified month. It is for those of us who haven’t mastered the Thirty days has September... rhyme or at least those of us who have not yet taught it to our computers. If, for example, we need to know what the last day of the month is in the column ENDDATE, we would type

SQL> SELECT ENDDATE, LAST_DAY(ENDDATE) FROM PROJECT;

How does LAST DAY handle leap years?

SQL>SELECT LAST_DAY('1-FEB-95') NON_LEAP,LAST_DAY('1-FEB-96')LEAP FROM PROJECT;

Analysis: We got the right result, but why were so many rows returned? Because we didn’t specify an existing column or any conditions, the SQL engine applied the date functions in the statement to each existing row. Let’s get something less redundant by using the following:

SQL>SELECT DISTINCT LAST_DAY('1-FEB-95') NON_LEAP,LAST_DAY('1-FEB-96') LEAP
FROM PROJECT;

This statement uses the word DISTINCT to produce the singular result

NON_LEAP  LEAP
--------- ---------
28-FEB-95 29-FEB-96

Unlike we, this function knows which years are leap years. But before we trust our own or our company’s financial future to this or any other function, check our implementation!

MONTHS_BETWEEN #

If we need to know how many months fall between month x and month y, use MONTHS_BETWEEN like this:

SQL> SELECT TASK,STARTDATE,ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE)DURATION
FROM PROJECT;

Wait a minute–that doesn’t look right. Try this:

SQL> SELECT TASK, STARTDATE,ENDDATE,MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION
FROM PROJECT;

Analysis:

That’s better. We see that MONTHS_BETWEEN is sensitive to the way we order the months. Negative months might not be bad. For example, we could use a negative result to determine whether one date happened before another. For example, the following statement shows all the tasks that started before May 19, 1995:

SQL> SELECT * FROM PROJECT WHERE MONTHS_BETWEEN('19 MAY 95', STARTDATE) > 0;

NEXT_DAY #

NEXT_DAY finds the name of the first day of the week that is equal to or later than another specified date. For example, to send a report on the Friday following the first day of each event, we would type

SQL> SELECT STARTDATE,NEXT_DAY(STARTDATE, 'FRIDAY')FROM PROJECT;

Analysis: The output tells us the date of the first Friday that occurs after our STARTDATE.

SYSDATE #

SYSDATE returns the system time and date:

SQL> SELECT DISTINCT SYSDATE FROM PROJECT;
SYSDATE
----------------
18-JUN-95 1020PM

If we wanted to see where we stand today in a certain project, we can type

SQL> SELECT * FROM PROJECT WHERE STARTDATE > SYSDATE;

Now we can see what parts of the project start after today.