Here’s a rundown of some ways to use SEQUEL’s CVTDATE function

At some point, almost all SEQUEL users need to work with dates that are stored differently in several databases. This article looks at some uses of the CVTDATE function to help you handle this common problem.

Using the CVTDATE function
SEQUEL has a unique date conversion function called convert date (CVTDATE). This function converts numeric or character date fields into System i date data-type fields. When you convert numeric or character values to System i date values, the system recognizes the values as dates. The System i then can handle all kinds of date calculations, such as counting days between dates, subtracting dates, or sorting by dates. Not only can you perform calculations on dates, but you can present difficult date formats (such as Julian dates) in more readable formats. The general syntax is:

CVTDATE(fieldname,fieldtype)

Enter the name of a numeric or character date field in the fieldname portion of the expression. Use the fieldtype to indicate to SEQUEL the format of the date, as it is stored in the file.

How SEQUEL looks at dates
Let’s look at how SEQUEL works with some common date formats.

Dates with century values
MDY1 is shorthand for dates represented as mmddyyyy. For instance, if your field name is SHIPDATE and it is stored as mmddyyyy (09302007), you have an MDY1 type date.

Date types of CYMD and CJUL must have a century digit preceding the year value. The century digit is zero (0) for years between 1900 and 1999 and one (1) for years between 2000 and 2099. For example, the CYMD representation of September 30, 2007, is 1070930.

Dates without a century value
SEQUEL interprets date types without a century value (MDY, DMY, YMD, JUL) by assuming years between 40 and 99 are the years 1940 to 1999, and years between 00 and 39 are the years 2000 to 2039. For example, a MDY-type date of 093007 would be read as September 30, 2007. However, 093040 would be September 30, 1940. If you have dates in your database, such as birth dates, that are earlier than 1940 and don’t have a century digit, contact technical support to determine the best way to handle your situation.

Creating one date from several fields
You can also use CVTDATE to unite dates that are separated into three or four fields containing century, year, month, and day values. The field names in the CVTDATE calculation must be specified in the following order: century (if available), year, month, day. CVTDATE can work with character or numeric fields and the value must be a valid date. To unite dates, use one of these syntaxes:

CVTDATE(yy or yyyy,mm,dd)

CVTDATE(cc,yy,mm,dd)

You can use the CVTDATE syntax in the SELECT, WHERE, or HAVING clauses. Like other calculated fields in SEQUEL, you can use the alias names of the converted date fields from the SELECT clause in other clauses, including the ORDER BY clause.

Controlling Date Styles
SEQUEL commands contain a date style (DTSTYLE) parameter. This parameter controls the date formats that are shown when you use CVTDATE. You can control the default style from the Define View or Define Table screen.

Working with dates containing zero or invalid dates
Data mapping errors result when records contain zero as the converted date because zero is not a valid date. You can use SEQUEL’s GREATEST and CASE functions to avoid these errors. The statement below converts any field with a date value of zero, or a value lower than January 1, 1940, to the date of 01/01/40:

CVTDATE(GREATEST (FIELDNAME,400101),FIELDTYPE)

The statement below changes zero values to null, producing output of N/A, using SEQUEL’s CASE function:

CASE FIELDNAME WHEN 0

THEN NULL

ELSE CVTDATE(FIELDNAME,FIELDTYPE) END

A data mapping error also occurs if there is invalid data in a date field, such as a date of 09/32/2007. In this case, you can identify the record number in the error message. If you have the proper authority, you can edit the date using using SEQUEL’s UPDATE command.

Calculating working days
Sometimes you need to calculate the number of working days (Monday through Friday) between two dates. For example, how would you calculate the number of working days between June 1, 2006 and June 17, 2006 using SEQUEL?

A SELECT statement is a good approach:

SELECT

CVTDATE(2006,6,1) NAME(a), date 1

CVTDATE(2006,6,17) NAME(b), date 2

DAYS(b)-DAYS(a) LEN(5,0) NAME(c), delta

FLOOR(c/7)*5 LEN(3,0) NAME(workdayweek), days in whole weeks

c MOD 7 LEN(1,0) NAME(d), restdays

DAYOFWEEK(a) NAME(da), day of week date 1

DAYOFWEEK(b) NAME(db), day of week date 2

CASE WHEN da=1 THEN 2 if date 1 is equal

WHEN da=7 THEN 2 Sunday or Saturday

ELSE da END I start on Monday

LEN(1,0) NAME(dc),

CASE WHEN db=1 THEN 6 if date 2 is equal

WHEN db=7 THEN 6 Sunday or Saturday

ELSE db END I stop on Friday

LEN(1,0) NAME(dd),dd-dc

LEN(1,0) NAME(de), helpfield

CASE WHEN da IN(1,7) AND db IN (1,7) THEN workdayweek {correction of weekends}

WHEN de<0 THEN workdayweek+5+de+1

ELSE workdayweek+de+1 END LEN(5,0) NAME(tot)

This solution isn’t perfect if date 1 is a Sunday and date 2 is a Saturday. But with some revision, you can still use this approach. SEQUEL is very flexible. Just use your imagination!

This entry was posted on Tuesday, November 20th, 2007 at 12:46 pm and is filed under SEQUEL. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.