Oracle – for when it was like that when you got there

Main menu

Post navigation

ORA-1810 and Other Dating Disasters

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

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 :

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…

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 :

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.

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…

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 ?

round(sysdate,’yyyy’) will return the start of the year (1st jan) closest to the current date. This means july-2010 becomes 1/jan/2011, while june-2010 becomes 1/jan/2010. This is really helpful for Australian financial years (which run 1-july to 30-june) because extract(year from round(sysdate,’yyyy’)) will give you the financial year number for the date.

Also don’t forget the months_between function. Handy for calculating a person age (divide result by 12).