Article

Enhance Your Reporting with Sequel Date Functions

IBM i
Boost your Sequel skills.
Posted:
January 6, 2017

Your data contains dates, no matter what industry you’re in or what information you’re reporting on. These dates might be stored as a true date data type, or stored as a character or numeric field. You need the ability to filter your data based on a date, utilize date arithmetic for comparison, or calculate length of time between dates—and then return the information in a format that’s useable.

Sequel Data Access makes it easy to work with your date fields, from converting the numeric or character field into a true date data type field to performing date calculations. Continue reading to learn how to convert non-date data type fields and get the benefits, such as date sorting, date calculations, and date selection over your converted date fields.

Build Your Sequel Skills. Find Training >

Not using Sequel? That's okay. You can keep reading to see for yourself how useful Sequel is for data reporting. Or you can take a step back and learn more about what Sequel can do >

In this article, you’ll find out how to:

  • Convert a numeric or character date field into a true date data type field
  • Work with invalid date values like a zero, blank, or a typo
  • Convert 100 Year Format (HYF) and calculate number of days between two dates
  • Find the beginning date of the current month, as well as the first and last date of the previous month
  • Perform day and time calculations from a timestamp field

Example 1: Converting Character or Numeric Date Values into a True Date Data Type

With Sequel, you can quickly convert a character or numeric date value into a date data type that allows you to easily perform date sorting and data calculations.

Complete the conversion using the CVTDATE function along with the field name and format style. The function handles a variety of formats and can be used on the SELECT or WHERE clause:

CVTDATE(expression,type)

Type Form Example
MDY mmddyy 123114
MDY1 mmddyyyy 12312014
DMY ddmmyy 311299
DMY1 ddmmyyyy 21121999
YMD yymmdd 141231
CYMD cyymmdd 1141231
YMD1 yyyymmdd 20141231
JUL yyddd 14365
CJUL cyyddd 114365
JUL1 yyyyddd 2014365

In the following example, a date field with a length of 8,0 decimal or 8 character as YYYYMMDD would have an expression like this: CVTDATE(Field, YMD1). If you have a Julian date as YYYYDDD, the expression would look like: CVTDATE(Field, JUL1).

SELECT

 

20140723 NAME(wrkdt) LEN(8,0) COLHDG("Work date " "YYYYMMDD") EDTCDE(L), cvtdate(wrkdt,ymd1) NAME(date1), 2014204 NAME(wrkdt2) LEN(7,0) COLHDG("Workdate" "YYYYDDD") EDTCDE(L), cvtdate(wrkdt2,JUL1) NAME(date2)

FROM sequel/sqlexec

 

Dates formatted in a Sequel report

Figure 1 - Results of Example 1

Example 2: Working with Invalid Date Values

When converting data to a date field, only valid values that correspond to a date on the calendar can be converted. Sequel’s VALID_DATE function can be used to check whether the value will convert to a date or not. It is most powerful when used within the CASE function for conditional logic.

The values that are valid will be converted. Invalid values will be replaced with a NULL value to avoid data mapping errors.

In the example below, the date value shown had a typo (too many days) and another has a default value of 01010001. To properly handle these, build a CASE function around the VALID_DATE and CVTDATE functions.

There are two parameters in the VALID_DATE function: the field and the type. The type must be in quotes and include the following values:

VALID_DATE(value, "type")

The valid date types and lengths are:

*MDY, *YMD, *DMY (6,7,8,9) - For use with 2 digit year forms with or without separators. 6/7 digit values are assumed to be without separators. 8/9 digit values are assumed to have a leading century digit.

*CMDY, *CYMD, *CDMY (7,9) - With or without separators, having leading century digit.

*JUL (5,6,7,8) - With or without separator. 5 and 6 digit values are assumed to be without separators. 7 and 8 digit values are assumed to have LONGJUL (yyyy) format.

*LONGJUL (7,8) - With or without separator. 4-digit year.

*USA, *ISO, *EUR, *JIS (8,10) - For use with 4 digit year forms with or without separators.

VALID_DATE function will return one of four values for each record:

1              TRUE or valid                                      -1            Invalid length

0              FALSE or invalid                                 -2            Other invalid type

For any value other than 1, the CVTDATE should not be done. The logic can be easily accomplished in a CASE statement and will help avoid data mapping errors: 

CASE  

WHEN VALID_DAT(Field, “*ISO”) = 1  THEN CVTDATE(Field, YMD1)

ELSE NULL

END

SELECT  startdt COLHDG("Character" "Start Date"), case when VALID_DATE(startdt,"*ISO")=1 and startdt<>'00010101' then CVTDATE(startdt,YMD1) else null end NAME(sdate) COLHDG("Actual" "Start Date")
FROM mylib/datefile

 

Working with invalid date values

Figure 2 - Results of Example 2

Example 3: Converting HYF and Calculating Number of Days Between Two Dates

With the DAYS function, you can quickly calculate the number of days between two dates such as an order date and ship date, or the number of days a patient was in the hospital.     Shown in the next example, we are calculating the actual order date from a 100 Year Format Date (YFD) field, and the number of days between the order date and today’s date (using Current Date function). 

The CURRENT DATE function returns today date, the date when the view is run.

CURRENT DATE

SELECT

 

 

42055 NAME(orddate) COLHDG("Order date" "100 Year Format"), cvtdate(19000101,ymd1)+orddate days+1 day NAME(orddate2) COLHDG("Actual Order date " "HYF is 01/01/1900"), current date NAME(today) COLHDG("Todays" "Date"), (days(current date)-days(orddate2)) NAME(diff) LEN(3,0) COLHDG("Number of Days" "since ordered")

FROM sequel/sqlexec

Use Hundred Year Format with Sequel reports

Figure 3 - Results of Example 3

Example 4: Finding the First and Last Dates of the Month

Next we have a view with several examples of working with a date data type field to calculate the first and last day of the month, number of days in a month, and date calculations. Knowing the first and last days of the month, the number of days in a month will help with runtime prompts for record selections and for billing statements.

The DAYS function returns the day count of a date, timestamp, date duration, or timestamp duration. The expression must be a date, timestamp, or a valid string representation of a date:

DAY(expression)

SELECT

Date("03/04/15") NAME(datefld) COLHDG("Date in File"), datefld-DAY(datefld) days+1 day NAME(firstday) COLHDG("First Day " "of Month "), Datefld-day(datefld) days+1 day+1 month-1 day NAME(lastday) COLHDG("Last Day" "of Month"), day(Datefld-day(datefld) days+1 day+1month-1 day) NAME(Daycnt1) COLHDG("# of Days" "in Month"), datefld+4 months NAME(trandate) COLHDG("Four Month " "Follow-up Date")

FROM sequel/sqlexec

 

Calculate first and last day of the month

Figure 4 - Results of Example 4

Example 5: Performing Day and Time Calculations from a Timestamp Field

In the following example, we have two timestamp fields (tstmpin and tstmpout). With these fields, we’re able to calculate how long a patient stayed in the hospital from days, hours, and minutes.

For reference, the first image shows the patient information about the time the person checked in and out of the hospital. To bill the patient correctly, the second image shows the results using the following select statement having Sequel’s DURATION function.

The DURATION function accepts two timestamps as input and returns a unit of time between them.

DURATION('unit', startTIMESTAMP, endTIMESTAMP)

Possible Units:

  • YEARS
  • MONTHS
  • DAYS
  • HOURS                
  • MINUTES
  • SECONDS
  • MICROSECONDS

Calculate duration in your Sequel reports

Figure 5 - Patient Timestamp Information

SELECT

 

 

name COLHDG("Patient " "Name"), Duration('days', tstmpIN, tstmpOUT) NAME(daycnt) LEN(3,0) COLHDG("BILLABLE Days"), hour(timestamp(dateout,timeout)-timestamp(datein,timein)) NAME(hourcnt) COLHDG("BILLABLE Hrs" " "), minute(timestamp(dateout,timeout)-timestamp(datein,timein)) NAME(mincnt) COLHDG("BILLABLE Min" " "), duration('minutes',tstmpIN,tstmpOUT)/60 NAME(totalhrs) LEN(10,2) COLHDG("BILLABLE" "Total Hours" "With Partial Hrs")

FROM heath/datetime2

 

Use TimeStamp Calculations in reports

Figure 6 – Customer’s Billing Information

It’s easy to overlook the importance of dates in your data and the way inconsistent formats complicate your tasks. Sequel date fields can help you overcome these challenges. They’re very powerful tools when you know how to use them.

The tips we’ve covered give you a greater level of control and flexibility when it comes to working with your data.

Boost Your Sequel Skills

Interested in learning more about Sequel? Discover the training options available to you and get the support you need to make the most of Sequel.

Interested in Sequel?

Related Products

Related Solutions