SQL functions - numeric and date

Speaker notes


Slide 1:

This presentation will deal with numeric and date functions.

Slide 2:

Notice that with round, it it is a whole number it still does not show the decimal places.

Slide 3:

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.

Slide 4:

Frequently in mathematical work, you want to know the remainder. MOD provides that information.

Slide 5:

CEIL and FLOOR are used to find the nearest whole number either greater than (CEIL) or less than (FLOOR) the decimal number being examined.

Slide 6:

Exponentiation is done with POWER.

Slide 7:

This would be helpful on a large file if you were looking for a positive balance, a negative balance or a 0 balance etc.

Slide 8:

Note that dual can also be written sys.dual.

When we come to conversions and formatting we will look at the 4 digit year.

Slide 9:

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.

Slide 10:

When MONTH and YEAR are used, they should be enclosed in single quotes.

Slide 11:

ROUND and TRUNC can be used when dealing with fiscal year problems.

Slide 12:

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.

Slide 13:

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.

Slide 14:

The conversion is done TO_CHAR and then the data is displayed using the specified format.

Slide 15:

ADD_MONTHS lets you pick a date that is ahead or back. This is useful for figuring out due dates, fees etc.

Slide 16:

NEXT_DAY tells the next time that a particular day of the week occurs.

Slide 17:

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.

Slide 18:

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.

Slide 19:

Other examples of conversions.

Slide 20:

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.

Slide 21:

Time conversion:

AM or PM

HH or HH12 or HH24

MM

SS

Slide 22:

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.

Slide 23:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY'

returns to using the current century as the default.

Slide 24:

Look very carefully at the elements of formatting here.

Slide 25:

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.

Slide 26:

There is a B editing character that can be used to suppress leading zeros.

Slide 27:

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.

Slide 28:

pay_id is defined as VARCHAR2.

Note that the second example works even without the conversion.

Slide 29:

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.

Slide 30:

NVL converts null values to actual values. The format is NVL(data that might have a null, value to be displayed).