The DAY and CURRENT functions

The following query returns the day of the month for the call_dtime and res_dtime columns in two expression columns.

Figure 1. Query

SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
   FROM cust_calls;

Figure 2. Query result

customer_num  (expression) (expression)

         106            12          12
         110             7           7
         119             1           2
         121            10          10
         127            31
         116            28          28
         116            21          27

The following query uses the DAY and CURRENT functions to compare column values to the current day of the month. It selects only those rows where the value is earlier than the current day. In this example, the CURRENT day is 15.

Figure 3. Query

SELECT customer_num, DAY (call_dtime), DAY (res_dtime)
   FROM cust_calls
   WHERE DAY (call_dtime) < DAY (CURRENT);

Figure 4. Query result

customer_num  (expression) (expression)
         106           12           12
         110            7            7
         119            1            2
         121           10           10

The following query uses the CURRENT function to select all calls except those that came in today.

Figure 5. Query

SELECT customer_num, call_code, call_descr
   FROM cust_calls
   WHERE call_dtime < CURRENT YEAR TO DAY;

Figure 6. Query result

customer_num  106
call_code     D
call_descr    Order was received, but two of the cans of ANZ tennis balls
              within the case were empty

customer_num  110
call_code     L
call_descr     Order placed one month ago (6/7) not received.
⋮
customer_num  116
call_code     I
call_descr    Second complaint from this customer! Received two cases
              right-handed outfielder gloves (1 HRO) instead of one case
              lefties.

The SYSDATE function closely resembles the CURRENT function, but the default precision of its returned value is DATETIME YEAR TO FRACTION(5), rather than the default DATETIME YEAR TO FRACTION(3) precision of CURRENT when no DATETIME qualifier is specified.