Dates and Times in DB2

1
Dates and Times in DB2

• Pam Odden

2
Objectives

• Learn what date and time durations are
• Learn how they are stored
• Use dates and times in arithmetic expressions
• Learn two methods of date and time validation

3
What is a Date or Time Duration?

• A duration is an elapsed period of time
• A date duration is stored in DB2 as DEC(8,0) with
  a format of YYYYMMDD.
  
• For example
• 00010604 1 year, 6 months, and 4 days
• It is stored in COBOL as you would store any
  other decimal value, PIC S9(8) COMP-3.
  
• A time duration is stored as DEC(6,0) with a
  format of HHMMSS.
  
• For example
• 062505 6 hours, 25 minutes, 5 seconds
• It is stored in COBOL as PIC S9(6) COMP-3.

4
Examples

• select current date, current date - '01/01/2002'
• from m7535db1.dummy_table
  
  
• 
  
  
• ---------------------------------------------
  -
  
• 02/07/2002 106.
• select current time, current time - '1210 AM'
• from m7535db1.dummy_table
  
  
• 
  
  
• ---------------------------------------------
  
  
• 0157 PM 134704.

5
LABELED DURATIONS

• A labeled duration expresses a date or time
  duration in easy English language
  
• 5 years 1 hour
• 1 month 10 minutes
• 60 days 30 seconds
• 5 microseconds
• select current time, current time 1 hour,
  current time 010000.
  
• from m7535db1.dummy_table
  
  
• 
  
  
• ---------------------------------------------
  ------------------
  
• 0206 PM 0306 PM 0306 PM
• select current date, current date 1 month 3
  days
  
• from m7535db1.dummy_table
  
  
• 
  
  
• ---------------------------------------------
  ------
  
• 02/07/2002 03/10/2002

6
Use scalar functions to access durations

• DB2 can identify the components in a duration.
• We can access them by using the scalar functions
  that refer to a portion of a date or time.
  
• select year(00100615.) as YEARS
• , second(034059.) as SECONDS
• from m7535db1.dummy_table
• ---------------------------------
• YEARS SECONDS
• ---------------------------------
• 10 59

7
Valid Arithmetic Operations using Dates and Times

• Addition
• Date Duration Date
• Time Duration Time
• Timestamp Duration Timestamp
• Subtraction
• Date Duration Date
• Time Duration Time
• Timestamp Duration Timestamp
• Date Date Duration
• Time Time Duration
•  

8
Date Arithmetic (cont.)

• Subtracting the later date from the earlier date
  gives a negative duration  
  
• select '2002-01-01' - current date
• from m7535db1.dummy_table
• ------------------------------------
• -106.
• Using a duration in standard arithmetic doesnt
  always make sense.
  
• select 120650. 35
• from m7535db1.dummy_table
• --------------------------
• 120685.
• Know what to expect when using months of
  differing numbers of days.
  
• select date('01/31/2002') 1 month,
• date('01/31/2004') 1 month,
• date('01/31/2002') 30 days
• from m7535db1.dummy_table
• -------------------------------------

9
What is M7535DB1.DUMMY_TABLE?

• select
• from m7535db1.dummy_table
• ---------------------------
• DUMMY SSID
• ---------------------------
• DUMMY TSN
• This is a table created by our DBA, guaranteed to
  hold a single row. It is designed to be used in
  SQL statements in which a table reference is
  needed, but the table contents are not important.
  It can be used to determine which system you are
  running against, ie. TSN.
• In DB2 v.5, IBM has included SYSIBM.SYSDUMMY1.
• It contains one column, IBMREQD, an indicator
  specifying Y if the row was supplied by IBM, N if
  it was not.

10
Determining the day of the week in DB2

• Use this expression to return an integer
  corresponding to the day of the week
  
• days(current date) (days(current date)/7) 7
•  
• 0 Sunday
• 1 Monday
• 2 Tuesday
• 3 Wednesday
• 4 Thursday
• 5 Friday
• 6 Saturday
• select current date,
  
  
• days(current date) - (days(current
  date)/7) 7
  
• from m7535db1.dummy_table
  
  
• ---------------------------------------------
  ---------
  
• 
  
  
• ---------------------------------------------
  ---------
  
• 02/07/2002 4

11
Dates and Times in Column Specifications

• Arithmetic expressions using dates and times can
  be used in the SELECT clause
  
• select ccsd_id, lastname, enterdate,
  
  
• integer(
  
  
• (day(enterdate - '08/30/2001')) / 7
  
  
• (month(enterdate - '08/30/2001'))
  52/12
  
• (year(enterdate - '08/30/2001')) 52
  )
  
• as WEEKS_LATE
  
  
• from ssasidb1.astu_student
  
  
• where schoolnum '201 order by WEEKS_LATE
   
  
• ---------------------------------------------
  -----------
  
• CCSD_ID LASTNAME ENTERDATE
  WEEKS_LATE
  
• ---------------------------------------------
  ----------
  
• 419776 FORD 02/06/2002
  22
  
• 143570 OLIVAS 01/11/2002
  18
  
• 126194 RODRIGUEZ 11/06/2001
  9
  
• 110823 PARRY 10/15/2001
  6
  
• 094729 SPARKS 09/24/2001
  3
  
• 124321 NEVINS 09/24/2001
  3
  
• 035570 ROBLES 08/30/2001
  0
  

12
Dates and Times in a Where clause

• Expressions using dates and times can also be
  used as selection criteria
  
• select 'LESS THAN 1 MONTH' as TIME_IN_DISTRICT,
• ccsd_id, enterdate, lastname
• from ssasidb1.astu_student
• where schoolnum '201'
• and enterdate current date - 1 month
• union all
• select '1 TO 3 MONTHS' as TIME_IN_DISTRICT,
• ccsd_id, enterdate, lastname
• from ssasidb1.astu_student
• where schoolnum '201'
• and enterdate
• and enterdate current date - 3 months
• union all
• select 'OVER 3 MONTHS' as TIME_IN_DISTRICT,
• ccsd_id, enterdate, lastname
• from ssasidb1.astu_student
• where schoolnum '201'

13
Dates and times in where clause (cont.)

• ---------------------------------------------
  
  
• TIME_IN_DISTRICT CCSD_ID ENTERDATE LASTNAME
  
  
• ---------------------------------------------
  
  
• LESS THAN 1 MONTH 167978 01/14/2002 NEZAM
  
  
• LESS THAN 1 MONTH 185167 01/23/2002 VARGAS
  
  
• LESS THAN 1 MONTH 196231 02/04/2002 PICHARDO
  
  
• LESS THAN 1 MONTH 217451 01/17/2002
  DICKNEITE
  
• 1 TO 3 MONTHS 143570 01/11/2002 OLIVAS
  
  
• 1 TO 3 MONTHS 143599 11/27/2001 BARNES
  
  
• 1 TO 3 MONTHS 153967 11/20/2001 NUESCA
  
  
• 1 TO 3 MONTHS 180331 11/27/2001 BARNES
  
  
• 1 TO 3 MONTHS 181806 01/08/2002
  HERNANDEZ
  
• OVER 3 MONTHS 094729 09/24/2001 SPARKS
• OVER 3 MONTHS 097400 08/30/2001 COLTON
• OVER 3 MONTHS 101147 08/30/2001 BRAN
• OVER 3 MONTHS 101213 08/30/2001 GARCIA

14
Validating Dates using DB2

• The following query returns an SQL code of zero
  if the date is valid, and 181 if not.
  
• 140-VALIDATE-ENTERED-DATA.
• MOVE 500B-JOBSTMMI TO WS-DATE-MM.
• MOVE 500B-JOBSTDDI TO WS-DATE-DD.
• MOVE 500B-JOBSTYYI TO WS-DATE-CCYY.
• MOVE WS-WORK-DATE TO VALID-DATE.
• EXEC SQL
• SELECT DATE(VALID-DATE)
• INTO VALID-DATE
• FROM M7535DB1.DUMMY_TABLE
• END-EXEC.
• IF (SQLCODE 0)
• MOVE WS-DATE-CCYYMDD TO
• ELSE
• (Display error mossage)
• END-IF.

15
Validating dates in COBOL

• A program called DATECALC is available as a
  called subroutine to perform the following
  functions
  
• 1) Add a number of days to a date, returning an
  updated
  
• date.
  
  
• 2) Subtract a number of days a date, returning
  an updated
  
• an updated date.
  
  
• 3) Subtract a date from a date, returning number
  of days
  
• between the 2 dates.
  
  
• 4) Verify a date for validity.
  
  
• 5) Verify a timestamp for validity.
  
  
• 6) Convert a gregorian date to a julian date.
  
  
• 7) Convert a julian date to gregorian.
  
  
• 8) Add hours, minutes, seconds or microseconds
  (or any
  
• combination of these) to a timestamp.
  
  
• 9) Subtract hours, minutes, seconds or
  microseconds (or
  
• any combination of these) to a timestamp.
  
  
• 10) Determine the day of week for a date.
  
  
• 11) Subtract a second duration from a timestamp.
  
  
• 12) Add a second duration to a timestamp.
  
  
• 13) Determine the second duration between 2
  timestamps.
  

16
Calling DATECALC

• Below is how S1020068 (Daily Stats) calls
  DATECALC to determine the day of the week
  
• COPYBOOK TO BE USED TO CALL DATECALC
• EXEC SQL
• INCLUDE DATELINK
• END-EXEC.  
• 5000-CALL-DATECALC.
  
  
• MOVE WS-RPTING-DT-KEY(74) TO
  DATECALC-BASE-DATE(14).
  
• MOVE '-' TO
  DATECALC-BASE-DATE(51).
  
• MOVE WS-RPTING-DT-KEY(12) TO
  DATECALC-BASE-DATE(62).
  
• MOVE '-' TO
  DATECALC-BASE-DATE(81).
  
• MOVE WS-RPTING-DT-KEY(42) TO
  DATECALC-BASE-DATE(92).
  
• SET CALC-DAY-OF-WEEK TO TRUE.
  
  
• CALL DATECALC-PGM USING DATECALC-LK.
  
  
• IF NO-DATE-ERRORS
  
  
• CONTINUE
  
  
• ELSE
  
  
• DISPLAY 'S1020068 ERROR CALLING
  DATECALC
  

17
Summary

• DB2 provides date and time duration structures as
  a means to use and store durations.
  
• Durations and the date functions can simplify
  date arithmetic.
  
• Use M7535DB1.DUMMY_TABLE or SYSIBM.SYSDUMMY1
  (v.5) for queries not requiring data from a
  table, such as in date validation.
  

18
Loose ends from last class

• Sue pointed out that in v.5 functions will be
  able to use indexes this is correct.
  
• I thought the following example from the book
  might not work, but it does.
  
• select ccsd_id, leavedate, drvtrndate,
• coalesce('(L) ' char(leavedate),
• '(D) ' char(drvtrndate),
• 'NO DATE')
• from ssasidb1.astu_student where schoolnum
  '552'
  
• CCSD_ID LEAVEDATE DRVTRNDATE
• -------------------------------------
• 000499 ---------- ---------- NO DATE
• 000510 ---------- ---------- NO DATE
• 000524 ---------- ---------- NO DATE
• 003608 10/29/2001 ---------- (L) 10/29/2001
  
  
• 003655 ---------- ---------- NO DATE
  
  
• 003759 01/15/2002 ---------- (L) 01/15/2002

19
There IS class next week

• to review DB2 Version 5 changes again.
• V. 5 will be installed in TSN on Feb. 23rd and in
  production on Mar. 9th