Date Handling


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 :

  1. TO_CHAR converts an Oracle date into a character string.

  2. TO_DATE converts a character string or number into an Oracle 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:

TO_CHAR( [1]

01/22/02

02/14/02

04/21/02

05/21/02

[1] Don't mind the heading; we just didn't specify a name .

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 graphics/2_icon.gif from TEST_HISTORY WHERE STUDENT_ID = '111111111'  ORDER BY DATE_TAKEN 

(2) In this script we have created a real column name ” Date_Taken ”by putting it right after the SELECT command. You can do this with any column you select.

and here are the results:

DATE_TAKEN

01/22/02

02/14/02

04/21/02

05/21/02

Here are a couple more results using different formatting commands:

 SELECT TO_CHAR(DATE_TAKEN, 'Month, DDth, YYyy') graphics/1_icon.gif from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID in (3,4) graphics/2_icon.gif 

(1) Notice the format for month, day, and year here, and the results below.

(2) This is an example of getting the dates for a student for particular exams.

And here's the resulting report:

TO_CHAR

May , 21ST, 2002

April , 21ST, 2002

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_CHAR(DATE

May, 21ST, 2002

April, 21ST, 2002

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:

TO_CHAR(DATE_T

May, 21, 2002, 4:5:18

April, 21, 2002, 4:4:8

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:

  • Add months. For example, the following code:

     SELECT ADD_MONTHS(DATE_TAKEN, 3) NextExam graphics/1_icon.gif from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID = 1 

    (1) We want to calculate the date for this person's next exam, which should be three months after taking Test 1.

    results in this report:

    NextExam

    22-APR-02

  • Subtract months (just add the minus sign). For example, the following code:

     SELECT ADD_MONTHS(DATE_TAKEN, -2)StudyTime from TEST_HISTORY WHERE STUDENT_ID = 111111111 and TEST_ID = 1 

    gives this result:

    StudyTime

    22-NOV-01

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:

  • Select LAST_DAY (DATE_TAKEN) END_MONTH from ...

  • Select MONTHS_BETWEEN (SYSDATE, DATE_TAKEN) LastExam from ...

  • Select NEXT_DAY (DATE_TAKEN, 'MONDAY') NewWeek from ...

  • Select LEAST ('21-MAR-02', DATE_TAKEN) OLDEST from ... ( GREATEST has the same format.)

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:

TEST_DATE_TIME

14/02/2002, 04:35:41

15/06/2002, 01:25:36

27/06/2002, 10:23:33

07/09/2002, 11:47:36

22/01/2002, 12:00:00

21/05/2002, 12:00:00

21/04/2002, 12:00:00

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.



Guerrilla Oracle
Guerrilla Oracle: The Succinct Windows Perspective
ISBN: 0201750775
EAN: 2147483647
Year: 2003
Pages: 84

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net