It's time to look at what we can do with dates. This is another huge topic, so we're going to cover just the basics. As you become more sophisticated with your reporting and programming, you will just jump for joy at what you can do in Oracle with dates. In fact, you can become a hero of the list servers if you develop this specialized expertise. In any event, you can become a big person on campus because almost all SQL programming classes play with dates, and many homework assignments require figuring out clever date manipulations. Once word gets out that you can do these, you'll have free coffee and tofu for life! To start, first you must simply accept the fact that to Oracle, a date is a real data type, and that Oracle includes with a date the hours, minutes, and seconds. Now don't get too worried; Oracle also gives you the tools to work with all these parts . Got it? When you define a date column, Oracle stores all this ”MM,DD, YY,HH,MM,SS ”and then gives you tools to format the data into what you want for your report, screen, or whatever. The two tools are TO_CHAR and TO_DATE :
Usually you will use an Oracle date column, so that's what we'll use in our examples. Now Oracle gives us more than 40 ways to format a date column, so you can go as wild as you'd like. I'll cover the most common ones here. Let's take our TEST_HISTORY table. We have a Date_Taken column that we want to appear on our reports . To satisfy everyone, however, we have to be able to print that date in various formats. Here are a couple of examples: To show all the test dates for a student in MM/DD/YY format, use this code: SELECT TO_CHAR(DATE_TAKEN, 'MM/DD/YY') from TEST_HISTORY WHERE STUDENT_ID = 111111111 order by date_taken; Here's the resulting report:
Here's how to clean up this simple listing above to change "TO_CHAR" to a real heading: SELECT ' 'TO_CHAR(DATE_TAKEN, 'MM/DD/YY') DATE_TAKEN from TEST_HISTORY WHERE STUDENT_ID = '111111111' ORDER BY DATE_TAKEN
and here are the results:
Here are a couple more results using different formatting commands: SELECT TO_CHAR(DATE_TAKEN, 'Month, DDth, YYyy') from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID in (3,4)
And here's the resulting report:
In the next example, look at the "fm" in front of "Month". It takes out the spaces between the month name and the comma: SELECT TO_CHAR(DATE_TAKEN, 'fmMonth, DDth, YYyy') from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID in (3,4) Here's the resulting report:
To get the hours, minutes, and seconds, just add them to the SELECT statement: SELECT TO_CHAR(DATE_TAKEN, 'fmMonth, DD, YYyy, HH:MM:SS') from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID in (3,4) The resulting report looks like this:
Note Do you remember that DATE is a unique Oracle data type? If you want to do any calculations on a string, first you have to turn it into a date, then do the manipulation. But, and here's the tricky part, the string must be in one of the 40 plus acceptable Oracle date formats. If it isn't, Oracle cannot turn it into a date. Here's what you can do with the date column:
Other commands you can use are LAST_DAY , MONTHS_BETWEEN , NEXT_DAY , GREATEST , and LEAST . The SQL code is similar to the examples just given:
Note The examples here show just the format for the commands. They may or may not make sense if you use the TEST_HISTORY file, but they will give you an idea of the syntax. One last note: You can use dates in your WHERE clauses. You can subtract two dates, and you can use the BETWEEN or IN operator, but be very careful using the LEAST or GREATEST command. LEAST and GREATEST work only if you first convert the dates using the TO_DATE command. Way back in Chapter 9 we talked about how you will not always get information in the format you really want. This is especially true of dates. Many systems keep dates in the format MM/DD/YY, and now you know that Oracle keeps the date in the format DD-MON-YYYY. So what can you do? If you try to load the date as is, it will fail. In the preceding paragraphs you saw some of the incredible formatting that Oracle allows, and you can use some of the same logic to handle incoming dates. Let's say that you're loading a history file containing all the tests that everyone has taken, in the standard format MM/DD/YYYY. To get the data into Oracle format, just do this when you define the field in SQL*Loader: TEST_DATE DATE 'MM/DD/YYYY' Oracle will now store the dates in its native DD-MON-YYYY format. To accommodate what your users are accustomed to, you can use the TO_CHAR commands to format the dates back to the other format when you're reporting the information. Note Here's an important note: I strongly suggest that from now on you always use YYYY whenever you have control over dates. Some systems will take a date such as 06/15/02 as June 15th, 1902, instead of 2002. To avoid any problems, get in the habit of always using all four digits for the year. I want to complete something that you've probably been wondering about since the last chapter: How do we really go about calculating the start and end times for the test? Oracle actually includes the time when you specify a column type of DATE , so you don't have to put it there. But how do you get access to it? Again, the various date formatting commands are at your disposal. For example, if you wanted to look at the test start times, you would just use this command: SELECT TO_CHAR(DATE_TAKEN, 'DD/MM/YYYY, HH:MI:SS') TEST_DATE_TIME from TEST_HISTORY; And these would be the results:
So there are two ways to capture the times: (1) use SYSDATE for both, or (2) extract the time from SYSDATE and store it in a separate column. |