Oracle date format

With the functions to_char and to_date, a date format can be used.

Example:
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:
Format mask Description
CC Century
SCC Century BC prefixed with -
YYYY Year with 4 numbers
SYYY Year BC prefixed with -
IYYY ISO Year with 4 numbers
YY Year with 2 numbers
RR Year with 2 numbers with Y2k compatibility
YEAR Year in characters
SYEAR Year in characters, BC prefixed with -
BC BC/AD Indicator *
Q Quarter in numbers (1,2,3,4)
MM Month of year 01, 02...12
MONTH Month in characters (i.e. January)
MON JAN, FEB
WW Weeknumber (i.e. 1)
W Weeknumber of the month (i.e. 5)
IW Weeknumber of the year in ISO standard.
DDD Day of year in numbers (i.e. 365)
DD Day of the month in numbers (i.e. 28)
D Day of week in numbers(i.e. 7)
DAY Day of the week in characters (i.e. Monday)
FMDAY Day of the week in characters (i.e. Monday)
DY Day of the week in short character description (i.e. SUN)
J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH Hournumber of the day (1-12)
HH12 Hournumber of the day (1-12)
HH24 Hournumber of the day with 24Hours notation (1-24)
AM AM or PM
PM AM or PM
MI Number of minutes (i.e. 59)
SS Number of seconds (i.e. 59)
SSSSS Number of seconds this day.
DS Short date format. Depends on NLS-settings. Use only with timestamp.
DL Long date format. Depends on NLS-settings. Use only with timestamp.
E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE The full era name
FF The fractional seconds. Use with timestamp.
FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM Fill Mode: suppresses blianks in output from conversion
FX Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only
RM The Roman numeral representation of the month (I .. XII)
RR The last 2 digits of the year.
RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC Century. BC dates are prefixed with a minus.
CC Century
SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH Spelled and ordinal format; 1 results in first.
TH Converts a number to it's ordinal format. For example 1 becoms 1st.
TS Short time format. Depends on NLS-settings. Use only with timestamp.
TZD Abbreviated time zone name. ie PST.
TZH Time zone hour displacement.
TZM Time zone minute displacement.
TZR Time zone region
X Local radix character. In america this is a period (.)

Number to Words Conversion

This is something useful only as a trick or a quick hack.

How do you get from a number to the same number spelled out in words.

Here is something that works perfectly well but was not intended to be used in the way this example shows. It is a mistreatment of the to_char function based on a manipulating a date for output.

SELECT n, TO_CHAR (DATE '-4712-01-01' + (n - 1), 'jspth')
  FROM (SELECT 1721058 n
          FROM DUAL);

one million seven hundred twenty-one thousand fifty-eighth

So it does work - just don't rely on it - it is after all a to_char on a date field.

Number to Words

A neat trick to convert a number to the same number in words ( 12 becomes twelve ).

select n, to_char(date '-4712-01-01' + (n-1),'jspth')
  from (select 1721058 n from dual);

Try it out and play with the NLS LANG settings for different results.

TRUNC

TRUNC is one of those functions in Oracle you use and cast away probably without realising the true power available in the command.

Most programmers are aware that to 'TRUNC' a number removes all information to the right of the decimal point. So for example

SELECT TRUNC(2.7) FROM DUAL;


TRUNC(2.7)
----------
      2

1 row selected.

Some programmers are aware that you can TRUNC a date data type to return the same date with the time portion set to midnight (00:00:00).

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') as NO_TRUNC ,
    TO_CHAR(TRUNC(SYSDATE),'DD/MM/YYYY HH24:MI:SS') as WITH_TRUNC
FROM DUAL;


NO_TRUNC            WITH_TRUNC      
------------------- -------------------
17/04/2007 10:35:00 17/04/2007 00:00:00

1 row selected.

And there most programmers knowledge of TRUNC stops. Probably because that is sufficient for most programming tasks that need to be taken care of. The other reason is, probably, that most programmers first encountered TRUNC while examining other people's code and thought that that was all there was to know about using this function. There is more to TRUNC than just this 'neat' trick. As with most inbuilt functions, TRUNC has been overloaded to accept additional arguments.

TRUNC no longer has to be wrapped in a SQL statement using the dual table but can also be use in direct variable assignments in PL/SQL as follows:

DECLARE
dt   DATE := TRUNC (SYSDATE);
BEGIN
NULL;
END;

Not the most elegant of code, but it does illustrate the point.

When dealing with number data types the additional argument is the number of decimal places to TRUNC to. Note that changing the number to negative replaces numbers to the left of the decimal separator with zeros.

SELECT TRUNC (77.77, 1) AS pos, TRUNC (77.77, -1) AS neg
FROM DUAL;


    POS        NEG
---------- ----------
   77.7         70

1 row selected.

For date data types TRUNC has been overloaded to accept a format mask. If you are familiar with casting date data types to their character equivalents using the TO_CHAR function you should be aware of format masks. The format masks I tend to use most are 'MM' for month and 'YYYY' for year, which give you the first day of the month and first day of the year respectively.

SELECT TO_CHAR (TRUNC (SYSDATE, 'MM'), 'DD-MON-YYYY') AS mon,
    TO_CHAR (TRUNC (SYSDATE, 'YYYY'), 'DD-MON-YYYY') AS yr
FROM DUAL;


MON         YR      
----------- -----------
01-APR-2007 01-JAN-2007

1 row selected.

So there you have it, some hidden functionality of a simple inbuilt function revealed to you. No doubt there may be more available features in later releases of Oracle, but for now 99% of my coding and SQL needs have been satisfied.

Remember that even the most innocuous of commands may have hidden features. Features that are only hidden in the manual waiting for you to discover them.

A Date With Oracle

Dates are funny things. First you think they are something to do with days of the year then you turn around and they are numbers. What do numbers and dates have in common?

Internally all dates, including the time portion of a day, are stored as a certain number of days since a base date. We are not really concerned with that we are more interested in the fact that 0.5 of a day is midday and 0.0 means midnight. All the other hours can be worked out once we realise that 1/24 is the same as saying 1 am.

This concept allows the accurate, to seconds - not tiny fractions of seconds, caculations on dates to be performed. We can state reliably that one date less another will give a number. We can add, or subtract, a number from a date. We cannot however add two dates together as this is quite meaningless, both to us as observers and internally to the database.

A date plus or minus a number gives a date, whereas a date less a date gives a number. The number result can be more meaningfully expressed as an interval. Luckily for us there are several built in functions to display this more meaningfully.

As an aside, I did once spend a few days writing routines to extract the difference between two dates and give a meaningful result back days:hours:min:sec. Then someone more junior pointed out the interval functions to me. My embarasment soon passed, when I realised that I now had interval routines that worked, and that maybe relying on past knowledge meant I was missing something that could make my life easier.