SQL functions - numeric and date
This presentation will deal with numeric and date functions.
Notice that with round, it it is a whole number it still does not show the decimal places.
Round runs up following the rules of mathematics and trunc truncates at the point specified.
I used 2 decimal places, the developer can use whatever is appropriate.
Frequently in mathematical work, you want to know the remainder. MOD provides that information.
CEIL and FLOOR are used to find the nearest whole number either greater than (CEIL) or less than (FLOOR) the decimal number being examined.
Exponentiation is done with POWER.
This would be helpful on a large file if you were looking for a positive balance, a negative balance or a 0 balance etc.
Note that dual can also be written sys.dual.
When we come to conversions and formatting we will look at the 4 digit year.
sysdate gets todays date from the system. The sysdate includes the time, even though it is not displayed in the default format. More about formatting coming up.
When MONTH and YEAR are used, they should be enclosed in single quotes.
ROUND and TRUNC can be used when dealing with fiscal year problems.
This table was created in 2000. On the next slide, we will look at the same basic command with the donor table which was created in 1999.
Year calculations using table created in 1999 gives is handled differently then if the table was created in 2000.
This test was made 03-JUN-00.
The conversion is done TO_CHAR and then the data is displayed using the specified format.
ADD_MONTHS lets you pick a date that is ahead or back. This is useful for figuring out due dates, fees etc.
NEXT_DAY tells the next time that a particular day of the week occurs.
Oracle can convert explicitly where the developer uses the conversion functions TO_CHAR, TO_DATE, TO_NUMBER.
Oracle implicit conversions are:
VARCHAR2 or CHAR to NUMBER
VARCHAR2 or CHAR to DATE
NUMBER or DATE to VARCHAR2
These conversions are done automatically.
Note that the format is enclosed in single quotation marks and is that there is a comma separating the field to be formatted from the formatting.
Other examples of conversions.
You can even combine SPTH to get spelled out ordinal numbers: FOURTH etc. NOTE: can be written SPTH or THSP
Don't worry about the spacing. We haven't got to that kind of setup.
AM or PM
HH or HH12 or HH24
Oracle assumes the current century.
There is a RR year format that masks the two digit century part of the year to the closest century. Years that go from 50 to 99 are assumed to be in the previous century while years that go from 00 to 49 are assumed to be in the current century.
To make the default RR instead of YY you can ALTER the current session with the command:
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR'
This works for the current session, it is lost when you log off.
Permanent changes can be made.
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY'
returns to using the current century as the default.
Look very carefully at the elements of formatting here.
TO_CHAR converts what were pure numbers to a character format that contains things like $ and comma which are not part of the numeric presentation.
There is a B editing character that can be used to suppress leading zeros.
Negative data can also be the result of a calculation. As shown in the notes, I multiplied fields by -1 and then showed them formatted.
pay_id is defined as VARCHAR2.
Note that the second example works even without the conversion.
Remember that the last two records have 19 as the first two characters while the rest have 20.
This is because the last two records were created with a session set to RR instead of YY.
The rest have 20 because they were created when the system date had 2000 as the year. This determines the century.
NVL converts null values to actual values. The format is NVL(data that might have a null, value to be displayed).