Enhance Your Reporting with Sequel Date Functions

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

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 >

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)

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).

Image

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

Image

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

Image

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)

Image

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

Image

Figure 5 - Patient Timestamp Information

Image

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.