Better to know some... than all
|
||||||||||||||||||||||||||||
|
Date and Time FunctionsWe live in a civilization governed by times and dates, and most major implementations of SQL have functions to cope with these concepts. This section uses the table PROJECT to demonstrate the time and date functions. NOTE:This table uses the Date data type. Most implementations of SQL have a Date data type, but the exact syntax may vary.
ADD-MONTHSThis 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. You could make a new schedule by typing INPUT: SQL> SELECT TASK, STARTDATE, ENDDATE ORIGINAL-END, ADD-MONTHS (ENDDATE, 2) FROM PROJECT; ANALYSIS: You will find that all the functions in this section work in more than one place. 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. However, ADD MONTHS does not work with other data types like character or number without the help of functions TO-CHAR and TO-DATE. LAST-DAYLAST-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, you need to know what the last day of the month is in the column ENDDATE, you would type INPUT: SQL> SELECT ENDDATE, LAST-DAY (ENDDATE) FROM PROJECT; How does LAST DAY handle leap years? INPUT: SQL> SELECT LAST-DAY ('1-FEB-95') NON-LEAP, LAST-DAY ('1-FEB-96') LEAP FROM PROJECT; ANALYSIS: You got the right result, but why were so many rows returned? Because you 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: INPUT: SQL> SELECT DISTINCT LAST-DAY ('1-FEB-95') NON-LEAP, LAST-DAY ('1-FEB-96') LEAP FROM PROJECT; This statement uses the word DISTINCT (see Day 2, "Introduction to the Query: The SELECT Statement") to produce the singular result. Unlike me, this function knows which years are leap years. But before you trust you’re own or your company's financial future to this or any other function, check your implementation! MONTHS-BETWEENIf you need to know how many months fall between month x and month y, use MONTHS-BETWEEN like this: INPUT: SQL> SELECT TASK, STARTDATE, ENDDATE, MONTHS-BETWEEN (ENDDATE, STARTDATE) DURATION FROM PROJECT; ANALYSIS: That's better. You see that MONTHS-BETWEEN is sensitive to the way you order the months. Negative months might not be bad. For example, you 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: NEW-TIMEIf you need to adjust the time according to the time zone you are in, the New-TIME function is for you. Here are the time zones you can use with this function:
You can adjust your time like this: Like magic, all the times are in the new time zone and the dates are adjusted. INPUT: SQL> SELECT ENDDATE EDT, NEW-TIME (ENDDATE, 'EDT','PDT') FROM PROJECT; NEXT-DAYNEXT-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, you would type INPUT: SQL> SELECT STARTDATE, NEXT-DAY (STARTDATE, 'FRIDAY') FROM PROJECT; ANALYSIS: The output tells you the date of the first Friday that occurs after your STARTDATE. SYSDATESYSDATE returns the system time and date, If you wanted to see where you stood today in a certain project, you could type INPUT: SQL> SELECT DISTINCT SYSDATE FROM PROJECT; INPUT: SQL> SELECT * FROM PROJECT WHERE STARTDATE > SYSDATE; |
|||||||||||||||||||||||||||