Time Functions

You can use the time functions DAY, MDY, MONTH, WEEKDAY, and YEAR in either the projection clause or the WHERE clause of a query. These functions return a value that corresponds to the expressions or arguments that you use to call the function. You can also use the CURRENT function to return a value with the current date and time, or use the EXTEND function to adjust the precision of a DATE or DATETIME value.

Using DAY and CURRENT Functions

Figure 182 returns the day of the month for the call_dtime and res_dtime columns in two expression columns.

Figure 182. Query

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

Figure 183. 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

Figure 184 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 184. Query

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

Figure 185. Query Result

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

Figure 186 uses the CURRENT function to select all calls except those that came in today.

Figure 186. Query

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

Figure 187. 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.

Using the MONTH Function

Figure 188 uses the MONTH function to extract and show what month the customer call was received and resolved, and it uses display labels for the resulting columns. However, it does not make a distinction between years.

Figure 188. Query

SELECT customer_num,
   MONTH (call_dtime) call_month, 
   MONTH (res_dtime) res_month
   FROM cust_calls

Figure 189. Query Result

customer_num   call_month    res_month

         106            6            6
         110            7            7
         119            7            7
         121            7            7
         127            7             
         116           11           11
         116           12           12

Figure 190 uses the MONTH function plus DAY and CURRENT to show what month the customer call was received and resolved if DAY is earlier than the current day.

Figure 190. Query

SELECT customer_num,
   MONTH (call_dtime) called, 
   MONTH (res_dtime) resolved
   FROM cust_calls
   WHERE DAY (res_dtime) < DAY (CURRENT)

Figure 191. Query Result

customer_num     called   resolved

         106          6          6
         119          7          7
         121          7          7

Using the WEEKDAY Function

Figure 192 uses the WEEKDAY function to indicate which day of the week calls are received and resolved (0 represents Sunday, 1 is Monday, and so on), and the expression columns are labeled.

Figure 192. Query

SELECT customer_num, 
   WEEKDAY (call_dtime) called, 
   WEEKDAY (res_dtime) resolved 
   FROM cust_calls
   ORDER BY resolved

Figure 193. Query Result

customer_num     called   resolved

         127          3
         110          0          0
         119          1          2
         121          3          3
         116          3          3
         106          3          3
         116          5          4

Figure 194 uses the COUNT and WEEKDAY functions to count how many calls were received on a weekend. This kind of statement can give you an idea of customer-call patterns or indicate whether overtime pay might be required.

Figure 194. Query

SELECT COUNT(*) 
   FROM cust_calls
   WHERE WEEKDAY (call_dtime) IN (0,6)

Figure 195. Query Result

(count(*))

         4

Using the YEAR Function

Figure 196 retrieves rows where the call_dtime is earlier than the beginning of the current year.

Figure 196. Query

SELECT customer_num, call_code, 
   YEAR (call_dtime) call_year,
   YEAR (res_dtime) res_year
   FROM cust_calls
   WHERE YEAR (call_dtime) < YEAR (TODAY)

Figure 197. Query Result

customer_num call_code call_year res_year 

         116 I              1997     1997
         116 I              1997     1997

Formatting DATETIME Values

In Figure 198, the EXTEND function displays only the specified subfields to restrict the two DATETIME values.

Figure 198. Query

SELECT customer_num, 
   EXTEND (call_dtime, month to minute) call_time,
   EXTEND (res_dtime, month to minute) res_time
   FROM cust_calls
   ORDER BY res_time

Figure 199 returns the month-to-minute range for the columns labeled call_time and res_time and gives an indication of the work load.

Figure 199. Query Result

customer_num call_time   res_time

         127 07-31 14:30
         106 06-12 08:20 06-12 08:25
         119 07-01 15:00 07-02 08:21
         110 07-07 10:24 07-07 10:30
         121 07-10 14:05 07-10 14:06
         116 11-28 13:34 11-28 16:47
         116 12-21 11:24 12-27 08:19