Chapter 6 – Date Functions

"An inch of time cannot be bought with an inch of gold."

- Chinese Proverb

Getting the System Date

The example above shows you how to get the system date. You must get them from sysibm.sysdummy1.

Extracting From a Timestamp

Above are the keywords you can utilize to extract the day, month and year from the sysibm.sysdummy1 table.

The EXTRACT Command

This is the Extract command. It returns a date part, such as a day, month or year, from a timestamp value or expression. It can be used in the SELECT list, the WHERE Clause or the ORDER BY Clause!

Using the EXTRACT Command to Extract Month, Day, Year

This is the Extract command. It returns a date part, such as a day, month, or year, from a timestamp value or expression. It can be used in the SELECT list, the WHERE Clause or in the ORDER BY Clause!

Extracting From a Date Column

The above example is designed to show you how to extract from a column that is defined as a date.

Extracting the Date and Time from the Timestamp

Above are the keywords you can utilize to extract the day, month and year from the sysibm.sysdummy1 table.

Formatting Dates Example

Above you can see an example of formatted dates using the TO_CHAR command.

Formatting Date Standards

Above you can see the varying format standards recognized in different regions of the world.

Adding and Subtracting Days from a Date

The example above shows how to add or subtract a number of days from a date or timestamp.

Adding Years, Months, Days, Hours and Seconds

Above are the keywords you can utilize to add years, months, days, hours, minutes and seconds.

Using the Add_Months Command

Use the Add_Months command to add months to a date.

Adding Years to a Date

Use the Add_Months command with a 12 to add a year to a date.

Add Five Years to a Date

Add five years to a date by using the Add_Months command and then using 12 * 5 in the function. You could always use 60 in the function, but why not let the system calculate the months for you? There is less chance of making a mistake.

Converting Character Data to a Date or Time

You can convert character data to a date or a time.

Timestamp DAYOFWEEK, DAYNAME and MONTHNAME

You can find the DAYOFWEEK from a Timestamp, but the result comes back as an integer (e.g., Sunday = 1). You can also use the DAYNAME to get the actual day (e.g., Sunday). You can even get the MONTHNAME (e.g., October).

Finding Orders That Happened on a Friday

We found all orders that happened on a Friday by using the Order_Date and the DAYNAME function.

NEXT_DAY Command Finds a Future Day of the Week

This example calculates the date on the following Monday from our Order_Date. The Monday date will be later than the Order_Date. We could also put in another day of the week.

Finding the Last Day of a Month

Above is a trick to find the last day of a month from a date column or the current timestamp.

Finding The Last Day of the Previous Month

Above is a trick to find the last day of the previous month from a date column or the current timestamp.

Getting the First Day of the Month

Above is a trick to find the first day of the month from a date column or the current timestamp.

Finding the Number of Days between Two Dates

When you subtract two dates, you get a number of days in between those two dates.

Resetting the Microseconds Back to Zero

The above example shows how to reset the microseconds back to zero.

Turning Date and Time into Characters

You can use the CHAR command to convert date and time values into character data, which you must do to concatenate.

Converting Character Data to a Timestamp

You can convert character data to a timestamp.

Finding Differences between Timestamps

syntax

The value of <n>

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Differences between Timestamps Fractions of a Second

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Find Differences between Timestamp Seconds and Minutes

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Find Differences between Timestamp Hours and Days

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Find Differences between Timestamp Weeks and Months

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Find Differences between Timestamp Quarters and Years

DB2 allows you to find the difference between two timestamps, but it does not account for leap year and it always assumes 30 days for each month. The function is called TIMESTAMPDIFF. This does not have perfect accuracy.

Formatting Dates

DB2 gives you many options for formatting dates. The next page will show an example.

Formatting Dates Example

Above you can see an example of formatted dates using the TO_CHAR command.

Formatting Dates Example

Above you can see an example of formatted dates using the TO_CHAR command.

Formatting Dates Example

Above you can see an example of formatted dates using the TO_CHAR command.

Formatting Timestamp Example

Above you can see an example of formatted dates using the TO_CHAR command.

Formatting Timestamp Example

Above you can seen an example of formatted dates using the TO_CHAR command.