DB2 for i: manage dates in SQL

As computer scientists we had to deal with the dates from the first programs developments with which we enjoyed playing to learn. Dates are a basic element for our data processing, whatever is the business category we work for. ERP, Documentals, BI, production processes, are just examples of areas in which the date object is fundamental.

Obviously, databases have always come to the aid of us, giving us countless features to be able to manipulate the dates in all their shades.

"DB2 for i" and the DATE type

Let's see how the integrated database into the IBM i system (as / 400) can help us in "playing" with dates. Suppose we have a file where each record has a DATE type field containing, precisely, dates. The file is called tabSample and it is in the mm library.

Suppose we are using an SQL query editor through ODBC, but we could also use the STRSQL interactive command on 5250 emulation, or the integrated SQL script executor with IBM's Access Client Solution.

Our file is this which we see as an example, each record has a DATE column of date type and containing a value which represents a calendar day.

How to find the record with today’s date
SELECT * FROM mm.tabSample WHERE data = Current_Date

How to find all the records of the current month
SELECT * FROM mm.tabSample WHERE month(data) >= month(Current_Date)

How to find all the records of 4 days ago
SELECT * FROM mm.tabSample WHERE data = Current_Date - 4 Days

How to find all the “first day of the month”
SELECT data, monthname(data) FROM mm.tabSample WHERE DAY(data)='01'

How to extract what the day of the week it is and the number it represents in the year
SELECT data, dayname(data), dayofyear(data) FROM mm.tabSample

How to find how many days of difference for each date there are, compared to today
SELECT data, days(current_date) - days(data) FROM mm.tabSample

How to find the current date and hour of the system
SELECT current_date, current_time FROM sysibm.sysdummy1

Other features on dates
IBM has made available many other features on its DB2 database to manage dates. Here is a list which also works on the TIME and TIMESTAMP types

DAYNAME Returns a mixed case character string containing the name of the day (e.g., Friday) for the day portion of the argument.

DAYOFWEEK Returns the day of the week in the argument as an integer value in the range 1-7, WHERE 1 represents Sunday.

DAYOFWEEK_ISO Returns the day of the week in the argument as an integer value in the range 1-7, WHERE 1 represents Monday.

DAYOFYEAR Returns the day of the year in the argument as an integer value in the range 1-366.

DAYS Returns an integer representation of a date.

JULIAN_DAY Returns an integer value representing the number of days FROM January 1, 4712 B.C. (the start of Julian date calendar) to the date value specified in the argument.

MIDNIGHT_SECONDS Returns an integer value in the range 0 to 86 400 representing the number of seconds between midnight and the time value specified in the argument.

MONTHNAME Returns a mixed case character string containing the name of month (e.g., January) for the month portion of the argument

TIMESTAMP_ISO Returns a timestamp value based on date, time or timestamp argument

TIMESTAMP_FORMAT Returns a timestamp FROM a character string that has been interpreted using a character template

TIMESTAMPDIFF Returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps

TO_CHAR Returns a character representation of a timestamp that has been formatted using a character template. TO_CHAR is a synonym for VARCHAR_FORMAT

TO_DATE Returns a timestamp FROM a character string that has been inter-preted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT

WEEK Returns the week of the year of the argument as an integer value in range 1-54. The week starts with Sunday

WEEK_ISO Returns the week of the year of the argument as an integer value in the range 1-53


Dates in string format YYYYMMDD
But how many times do we find a date field which has actually been stored in string format, the classic YYYYMMDD? Historically older programs used this format, which made easier operations like sorting or formatting the value’s printing. But with a date field made like this can i still manipulate dates with DB2 features? Of course yes, let’s immediately see how the CAST operation can come to our help.

Suppose we have a file where each record has a CHAR or VARCHAR field type just containing the dates in format YYYYMMDD. The file is named tabSample and it’s in the mm library.

Our file is this which we see as an example, each record has a YYYYMMDD date column of VARCHAR(14) type and containing a value which represents a calendar day in YYYYMMDD format.

The DB2 CAST operator allows to convert the data from one format to another, if compatible.
Knowing what the dates’ ISO format should be, we convert the string into a date using the following syntax.

SELECT dataYYYYMMDD, cast(substr(dataYYYYMMDD,1,4) || '-' || substr(dataYYYYMMDD,5,2) || '-' || substr(dataYYYYMMDD,7,2) as date) FROM mm.tabSample

At this point we can apply all the previous dates examples also to this typology of date field in the string format.

How to find the record with the today’s date

SELECT * FROM mm.tabSample WHERE cast(substr(dataYYYYMMDD,1,4) || '-' || substr(dataYYYYMMDD,5,2) || '-' || substr(dataYYYYMMDD,7,2) as date) = Current_Date

... and so on for all the other examples

We have seen some examples of how we can manipulate dates with the integrated database of IBM i systems. Obviously on the web you will find other examples and features on dates, but now you will have a better awareness that with the dates you can play, and even easily.

Marco Moret Monitoring Project Manager at smeup ICS
 You can also find the article on LinkedIn

All articles
 Writers Manager: Monitor and manage printers with SQL
 Checking security on IBM i systems: the Audit Journal
 The first JDBC connection from C# to IBM i
 JTOpen, develop applications in .Net for IMB i
 DB2 for i: manage dates in SQL
 Get system info in SQL

Manage, analyze and control your Systems IBM i
Download, install and use in seconds
  IBM i Monitoring Facility - Check is completely free until the end of the month