During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).
The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query
SELECT TO_DATE('12/06/1992 2:19 PM', 'DD/MM/YYYY HH:MM PM' ) FROM dual;
At this point, I should come over all knowledgable and say something like “of course the answer is that the date format is incorrect and this will return ORA-1810 : format code appears twice”.
The fact of the matter is that I spent some considerable time looking at this before, in desparation , I wrote the query out again, and got it to run. It took me a while to spot that the Minutes format was specified as MM rather than MI and the query should look like this :
SELECT TO_DATE('12/06/1992 2:19 PM', 'DD/MM/YYYY HH:MI PM' ) FROM dual;
Note to self – don’t try and debug anything before that first cup of coffee in the morning.
Just to keep you on your toes, what if you tried the query using a different month format ?
SELECT TO_DATE( '12-JUN-1992 14:19:59', 'DD-MON-YYYY HH24:MM:SS') FROM dual;
This will actually return a different error – ORA-1816 – month may only be specified once.
Now, you’d expect this to work the same way if you’re converting a date to a character string as the other way around…
SELECT TO_CHAR( SYSDATE, 'DD-MM-YYYY HH24:MM:SS') FROM dual / TO_CHAR(SYSDATE,'DD-MM-YYYYHH24:MM:SS') --------------------------------------------------------- 20-02-2011 18:02:59
Oh. It has run, but it’s given us the month numeral instead of the minutes. No sign of either error though.
The Dangers of Implicit Conversion
This is all very interesting, but not that much of a problem is it. After all, Oracle does implicit conversions between dates and strings…
SELECT employee_id, start_date, end_date FROM hr.job_history WHERE end_date < '01-JAN-98' / EMPLOYEE_ID START_DATE END_DATE ----------- ------------------ ------------------ 101 21-SEP-89 27-OCT-93 101 28-OCT-93 15-MAR-97 200 17-SEP-87 17-JUN-93
All working fine then. Look, let’s try it again…
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY' / SELECT employee_id, start_date, end_date FROM hr.job_history WHERE end_date < '01-JAN-98' / Session altered. no rows selected
As we can see, the value to which the string is converted does rather depend on the NLS_DATE_FORMAT setting in the database the query is run against.
You could of course issue the alter session command in every date script to make sure that the conversion is as you expect. However, this does rather rely on you having the ALTER SESSION privilege on each database that you’d want to run this on.
The only way to be absolutely sure therefore, is to explicitly convert the date, specifying the format you want to convert to.
Hot Dating Tips
Number 1 – when meeting a lady for the first time, do not try to impress her with your literary prowess by boasting “I really enjoy a good Dickens”.
Number 2 – Dates are numbers. This fact is so widely known it’s almost forgotten. What it means is that there are a whole host of number functions that can be applied to dates.
If you want to return all records where the date is within a given date range, you could do this :
SELECT emp.first_name, emp.last_name, jh.end_date FROM hr.employees emp, hr.job_history jh WHERE jh.employee_id = emp.employee_id AND end_date >= TO_DATE('28-OCT-93', 'DD-MON-RR') AND end_date <= TO_DATE('01-JAN-98', 'DD-MON-RR') /
However, you can save yourself some typing by using the BETWEEN operator …
SELECT emp.first_name, emp.last_name, jh.end_date FROM hr.employees emp, hr.job_history jh WHERE jh.employee_id = emp.employee_id AND end_date BETWEEN TO_DATE('28-OCT-93', 'DD-MON-RR') AND TO_DATE('01-JAN-98', 'DD-MON-RR') /
TRUNC is another very useful function, particularly if your database is riddled with records entered from a Forms application which habitually stripped the time element off the date to default all date information to midnight.
Time for a quick demo…
CREATE TABLE hot_date ( location VARCHAR2(30), meeting_time DATE) / INSERT INTO hot_date( location, meeting_time) VALUES( 'The Pub', TO_DATE( '27-FEB-2011 20:00', 'DD-MON-YYYY HH24:MI')) / INSERT INTO hot_date( location, meeting_time) VALUES( 'Cinema', TO_DATE(SYSDATE, 'DD-MON-YYYY')) /
I want to find out if I’ve got a date today ( with the lovely Deb of course) ….
SELECT * FROM hot_date WHERE meeting_time = SYSDATE / no rows selected
Disaster ! I’ve not returned any records. Even a bunch of flowers from the all-night petrol station isn’t really going to get me out of this one. If only I’d done this…
SELECT * FROM hot_date WHERE TRUNC(meeting_time) = TRUNC(SYSDATE) / LOCATION MEETING_TIME -------------------- -------------------- Cinema 20-FEB-2011
Phew. That’s better. Note that you don’t have to TRUNC the meeting_time column…provided your confident that the data in it will never have a time element ( i.e. always be set to midnight).
If you do find yourself needing to do this on date columns that are indexed, you may want to consider adding a function based index.
Some other useful date stuff
A few other odds and ends to wrap up with…
LAST_DAY will return the last day of the month of the date you enter :
SELECT LAST_DAY(SYSDATE) FROM dual /
Now, there is no FIRST_DAY function, but that’s not much of a problem really…
SELECT TO_DATE( '01-'||TO_CHAR( SYSDATE, 'MON-YYYY'), 'DD-MON-YYYY') FROM dual /
ADD_MONTHS returns the date plus the number of months you specify. This is quite handy as it will also take a negative number …
SELECT ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(SYSDATE, -1) FROM dual
You can add and subtract from dates. By default, the numeral you specify will be treated as the number of days. So….
SELECT SYSDATE – 1 FROM dual;
will return SYSDATE from 24 hours ago. If you wanted to work in hours ( e.g. you want to query all the entries in a log table made within the last hour), you could do something like …
SELECT * FROM my_log_table WHERE log_timestamp > SYSDATE – (1/24);
If you want to look at timing information in PL/SQL, there’s always the handy DBMS_UTILITY.GET_TIME. Have a look here for an example of how this works.
I’m sure that any Oracle gurus reading this ( hello German) will have their own helpful hints to add, but in the meantime, I’m off to make myself presentable for the big night. Now, should I go au naturelle, or should I really shave my palms ?