/*===
    CS 325 - Week 14 Asynchronous Material

    Some useful SQL string- and date- and time-related functions
    (to give a TASTE of some of the possibilities!)
===*/

spool 325lect14-2-out.txt

/*===
    reminder: sysdate

    expects no arguments, returns the current system date (and time
    it turns out...)
===*/

select sysdate
from   dual;

/*===
    date- and time-related function:
    to_char

    *   expects a date or number and a format string,
        and returns a character-string version of that date or number
        based on the format;
===*/

select empl_last_name, hiredate
from   empl;

prompt just display the name of the month of hire

select empl_last_name, to_char(hiredate, 'MONTH') "MONTH HIRED"
from   empl;

select empl_last_name, to_char(hiredate, 'Month') "Month Hired"
from   empl;

prompt a few more possibilities at once:

select to_char(sysdate, 'YYYY') year,
       to_char(sysdate, 'Mon YYYY') mon_year,
       to_char(sysdate, 'MM-DD-YY') num_version,
       to_char(sysdate, 'Day, Month DD, YYYY') long_version,
       to_char(sysdate, 'DY - Mon DD - YY') brief_version
from   dual;

/*===
    what about the time part?

    'HH12' -  hour of the day, 12-hour clock
    'HH24' -  hour of the day, 24-hour clock
    'MI' - minutes of the hour
    'SS' - seconds of the minute
    'AM' - displays AM or PM depending on the time
===*/

select to_char(sysdate, 'D DD DDD Day, Mon YYYY - HH12 HH24 MI SS AM') 
from   dual;

/*===
    a few more date-related operations and functions:

    you can add, subtract from a column whose type is date:
===*/

select sysdate + 1 "tomorrow"
from dual;

select sysdate - 1 "yesterday"
from dual;

/*===
    ...but adding, subtraction from a date *literal* is trickier.
    and that's where to_date comes in handy:
===*/

prompt cannot add to a date literal string

select '31-DEC-2021' + 1
from   dual;

/*===
    function to_date has a version that expects a date-string,
    and returns the corresponding date
===*/

select to_date('31-DEC-2021') + 1
from   dual;

select to_char(hiredate, 'HH12:MI AM') hiretime
from   empl;

/*====
    what if, then, you want to insert a given date AND time
    into date column?

    PIECE 1: to_date has a TWO argument version, also,
        where the 2nd argument is a format string specifying
        the date/time format being used (and CAN include time that way)
=====*/

commit;

prompt inserting an employee hired on November 15th of 2021 at 2:37 pm

insert into empl(empl_num, empl_last_name, hiredate)
values
('1111', 'Hughes', to_date('2021-11-15 2:37 pm',
                           'YYYY-MM-DD HH12:MI AM'));

select empl_last_name, 
       to_char(hiredate, 'Month Day, YYYY - HH12:MI AM') hire_date_time
from   empl;

prompt (now removing this employee... 8-) )

rollback;

/*----
    add_months - expects a date and a number of months,
        and returns the date that many months from the given date
----*/

select add_months(to_date('30-Jan-2022'), 1)
from   dual;

/*----
    months_between - expects two dates, and returns the number
        of months between those two dates
----*/

select months_between(sysdate, to_date('30-Jan-2022'))
from   dual;

select months_between(to_date('30-Jan-2022'), sysdate)
from   dual;

/*----
    and a few STRING-related functions:
----*/

/*----
    initcap - expects a string, and returns a version of that string
    with an initial uppercase letter
----*/

select initcap('SILLY') looky1, initcap('silly') looky2
from   dual;

/*-----
    lower - expects a string, returns an all-lowercase version of it
    upper - expects a string, returns an all-uppercase version of it
----*/

select lower(empl_last_name), upper(empl_last_name)
from   empl;

/*----
   lpad - left pad - expects a string, a desired result length, and
      a padding character, and returns a string of the desired length
      with the given string padded on the left with that padding
      character
----*/

select lpad(empl_last_name, 12, '.') ldots
from   empl;

/*----
   rpad - right pad - expects a string, a desired result length, and
      a padding character, and returns a string of the desired length
      with the given string padded on the right with that padding
      character
----*/

select rpad(empl_last_name, 12, '.') rdots
from   empl;

/*----
    remember, you can compose function calls as long as the return
    types are compatible...!
----*/

select lpad( to_char(hiredate, 'Day'), 14, ' ')
       || to_char(hiredate, '- Month YY') "Hiredate"
from empl;

/*---
    ltrim - expects a string, and returns that string with
       any leading blanks (starting the string) removed

    rtrim - expects a string, and returns that string with
       any trailing blanks (ending the string) removed
---*/

col nicer format a30

select ltrim('   Hi   ') leftchop,
       rtrim('   Hi   ') rtchop,
       rtrim( to_char(sysdate, 'Day') )
           || ', '
           || rtrim( to_char(sysdate, 'Month') )
           || ' ' 
           || rtrim(to_char(sysdate, 'DD, YYYY')) nicer
from dual;

/*---
    length - expects a string, returns the number of characters in
        that string

    substr - expects a string, 
             the position to start in the string (first char is position 1?!),
             and how long a substring is desired, 
             and returns the substring starting at that position and going
                 that many characters;

             (2-argument version just grabs the REST of the string
             at the given 1-based position)
---*/

col rest format a10

select empl_last_name,
       length(empl_last_name) length,
       substr(empl_last_name, 1, 3) abbrev3,
       substr(empl_last_name, 4) rest
from   empl;

spool off
clear columns