DATE Processing

Much of the time spent processing dates is dedicated to storage and reference. Yet, there are times that one date yields or derives a second date. For instance, once a bill has been sent to a customer, the expectation is that payment comes 60 days later. The challenge becomes the correct calculation of the exact due date.

Since Netezza stores the date as an number, it allows simple mathematics to calculate new dates from dates. The next SELECT operation uses the Netezza date arithmetic to show the month and day of the payment due date in 60 days:

SELECT  Order_date+60 "Due Date"

  ,Order_date

  ,to_char(Order_total,’$99,999.99’)

FROM Order_table

WHERE Order_date > ‘2004-12-31’ ;

4 Rows Returned

Due Date order_date order_total

2005-03-02 2005-01-01  $ 8,005.91

2005-03-21 2005-01-20  $ 12,347.53

2005-03-02 2005-01-01  $ -1.00

Besides a due date, the SQL can also calculate a discount period date 10 days prior to the payment due date using the alias name:

SELECT  Order_date

  ,Order_date +60 AS Due_Date

  ,to_char(Order_total,’$999,999.99’)

  ,order_date +50 "Discount Date’)

  ,to_char(Order_total*.98,’$99,999.99’) "Discounted"

FROM Order_table

WHERE Order_date > ‘2004-12-31’ ;

4 Rows Returned

order_date due date order_total Discount Date Discounted

2005-01-01 2005-03-02 $ 8,005.91 2005-02-20 $ 7,845.79

2005-01-20 2005-03-21 $ 12,347.53 2005-03-11 $ 12,100.58

2005-01-01 2005-03-02 $ -1.00 2005-02-20 $ -.98

In the above example, it was demonstrated that a DATE + or - an INTEGER results in a new date (date { + | - } integer = date). However, it probably does not make a lot of sense to multiply or divide a date by a number.

Since the format of the date is YYYYMMDD, that when the NPS sees a number being added to a date, and knows that DD (days) is the lowest component, the 60 being added to the order date in the above SELECT is assumed to be the number of days. The system is smart enough to know that it is dealing with a date. Therefore, it is smart enough to know that a normal year contains 365 days.

The associative properties of math tell us that equations can be rearranged and still be valid. Therefore, a DATE – a DATE results in an INTEGER (date +|- date = integer). This INTEGER represents the number of days between the dates.

This chart summarizes the math operations on dates

Figure 8-3

This SELECT uses this principal to display the number of days I was alive on my last birthday:

SELECT (date ‘2004-10-01’) - (date ‘1952-10-01’) "Mike’’s Age In Days";

1 Row Returned

Mike’’s Age in Days

18993 

The above example subtracted one of my birthdays (October 1, 2004) with my actual birthday in 1952. Notice how awful an age looks in days! Notice that I attempted to use two single quotes to store or display a literal single quote in a character string. This works in most databases, but not on the NPS.

As mentioned above, an age in days looks awful and that is probably why we do not use that format. I am not ready to tell someone I am just a little over 17000. Instead, we think about ages in years. To convert the days to years, again math can be used as seen in the following SELECT:

SELECT ((date ‘2004-10-01’) - (date ‘1952-10-01’))/365 "Mike’’s Age In Years";

1 Row Returned

Mike’'s Age in Years

52 

Wow! I feel so much younger now. This is where division begins to make sense, but remember, the INTEGER is not a DATE. At the same time, it assumes that all years have 365 days. It only does the math operations specified in the SQL statement.

Now, what day was he born?

The next SELECT uses the concatenation, date arithmetic and a blank TITLE to produce the desired output:

SELECT 'Mike was born on day ' ||

mod(((date '1952-10-01') - (date '1900-01-01')),7) AS " " ;

1 Row Returned

Mike was born on day 2

The above subtraction results in the number of days between the two dates. Then, the MOD(<value>,7) divides by 7 to get rid of the number of weeks and results in the remainder. A MOD of 7 can only result in values 0 thru 6 (always 1 less than the MOD operator). Since January 1, 1900 is a Monday, Mike was born on a Wednesday.

This chart can be used for the day of the week based on the above formula and January 1, 1900

Figure 8-4