You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/

If you are looking for a better way to access and develop data on DB2, Netezza, or any system, I hope you will download my Nexus Query Chameleon software, which works on all systems and is the best integration and query federation tool ever built. It has taken 19 years to join 20 tables spanning 20 different systems in a single query, but nobody else is even close to that capability. You can now use Nexus to join tables and views from DB2 and Netezza with Excel worksheets and Microsoft Access, and any combination of cloud and on-premises databases in a single query. Check out the video below and see Nexus in action.

The example below demonstrates that the current_date will return today’s date.  Add or subtract a number from the CURRENT_DATE, and you are adding or subtracting days from the date.  If you subtract from two dates, you get the number of days between the two dates.

Below are the keywords you can utilize to get the date, time, or timestamp.  These are reserved words that the system will deliver to you when requested.

As well as creating a TIME data type, intelligence has been added to the clock software.  It can increment or decrement TIME with the result increasing to the next minute or decreasing from the previous minute based on the addition or subtraction of seconds.

Netezza allows you to see the date and time with the NOW() function.  The next time someone asks for the time tell them NOW.

Netezza allows you an extended version of a time stamp that is robust and verbose.

A DATE – DATE is an interval of days between dates.  A DATE + or – Integer = Date.  The query below uses the dates the traditional way to deliver the Interval.

A DATE – DATE is an interval of days between dates.  A DATE + or – Integer = Date.  Both queries below perform the same function, but the top query uses the date functions to find “Days” and the query on the bottom finds “Years”.

The TIMESTAMP function below is a wonderful feature.  Netezza allows you to convert a date or a combination of a date and a time into a Timestamp.  The example below shows an example of converting a date, a date and time, and a literal date and time.  This should be all you need.

If you add or subtract a number from a date, it adds or subtracts several days from the date.

The example below uses the Add_Months Command. What you can do with it is add a month or many months to your date columns.   Can you convert this to one year? Unfortunately, there is no ADD_YEAR command!

The example below uses the Add_Months Command. What you can do with it is add a month or many months to your date columns. Unfortunately, there is no ADD_YEAR command, but 12 months makes one year!

The example below uses the Add_Months command.  The example below shows how to add five years by using math easily.  Sixty months works fine, but it is better to let the system do the math.

You can use the TO_CHAR function to format dates. You can check out the Formatting Dates on Netezza blog to see thousands of examples.

The TO_CHAR command will take a value and convert it to a character string, which is how we got our dollar signs in our totals.

The TO_CHAR command will take a value and convert it to a character string. The examples below format the column salary using different format techniques.

You can use the TO_CHAR and TO_DATE functions for date related conversions.

The Extract command extracts portions of a date, time, or timestamp.

The example below shows how robust the EXTRACT command is at extracting portions of a date, time, or timestamp.

Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function, and the example below is designed to show how to use it with literal values.

The examples below use the date_part function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes.

You can also use the to_char extension to extract portions of a date.

It was mentioned earlier that Netezza stores a date as an integer and therefore allows math operations to be performed on a date. Although the EXTRACT works great and it is ANSI compliant, it is a function.  Therefore, it must be executed, and the parameters passed to it to identify the desired portion as data.  Then, it must pass back the answer.  As a result, there is additional overhead processing required to use it.

The next SELECT operation uses entirely ANSI compliant code to show the month and day of the payment due date in two months and four days.  Notice it uses double quotes to allow reserved words as alias names.

The example below demonstrates the SELECT using the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY.  Notice the Answer Set headers.

The examples below use the Date_Trunc function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes. Date_Trunc selects to which precision to truncate the input value. The return value is of type timestamp or interval. The valid text names are microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, and millennium.

The DATE_TRUNC command will set the hour to the top of the hour. Likewise, it will set the minute to the top of the minute and seconds to the top of the seconds.

The DATE_TRUNC command sets the year to the 1st day of the year, the month to the first day of the month, and the day to midnight for the time.

The LAST_DAY command returns the last day of the month based on a given date or timestamp.

The NEXT_DAY function will give you the next date of the day of the week you specify based on the current date or the date you specify. In our example below, the timestamp of the next Saturday from the date of ’01-10-2023′ is the fourteenth. The weekday argument is a three-character string that indicates the day of the week (‘SUN’, ‘MON’, ‘TUE’, ‘WED’, ‘THU’, ‘FRI’, ‘SAT’) for which the date is to be returned. It must be enclosed in single quotation marks and can contain either uppercase or lowercase letters. Any characters that are specified after the third character are ignored.

You can use the tricks below to get the first day of the month or the last day of the previous month.

MONTHS_BETWEEN returns the number of months between two DATE or TIMESTAMP values. If the first date is less than the second date the months between will be a negative number. If the first date is after the second date it will return a positive number. Notice the ::DATE keyword in the second example below, which is another way to CAST a value to a different data type for the life of the query.

We have used two different examples below to get the number of days between two timestamps. 

To subtract one date or time stamp from another, use the AGE function. When the AGE function only receives one parameter the second parameter defaults to the current_timestamp.

The example below shows you how to use day and month intervals.

Intervals add or subtract a period from a date at specific intervals. For example, in our examples below, we have a one-month interval from a January 29th date.  Notice how differently the interval works if it is a leap year. Also notice how we can cast the interval to return a date.

Below, we are selecting the current_time and then converting a Timestamp to Time (and adding 5 hours).

Netezza allows you to convert character strings into a Timestamp.  Notice that both answers below are exactly the same.  The second parameter is NOT how the data should be output or formatted, but instead it reflects how the string should be interpreted. 

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

A Netezza session can modify the time zone during normal operations without requiring a logoff and logon.  At this time, the NPS only recognizes time zone processing stored in a table with data type of TIME WITH TIME ZONE.   Hopefully, it will soon also be added to TIMESTAMP when stored in a table.

The examples below set the time zone and then query Current_Timestamp simultaneously.

When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time.  Netezza provides a Boolean function to make this test for you.  It is called OVERLAPS; it evaluates true, if multiple points are in common, otherwise it returns a false. The literal is returned because both date ranges have from October 15 through November 30 in common.

The example below tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal. The literal was not selected because the ranges do not overlap.  So, the common single date of November 30 does not constitute an overlap.  When dates are used, 2 days must be involved, and when time is used, 2 seconds must be contained in both ranges.

The example below tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal. This is a tricky example, and it is shown to prove a point.  At first glance, it appears as if this answer is incorrect because 02:01:00 looks like it starts 1 second after the first range ends.  However, the system works on a 24-hour clock when a date and time (timestamp) is not used together.  Therefore, the system considers the earlier time of 2AM time as the start and the later time of 8 AM as the end of the range.  Therefore, not only do they overlap, the second range is entirely contained in the first range.

Posted in Blog