Working with dates, times, and time stamps When you start analyzing data, you begin to realize how much of it is time-sensitive. As a result, you may have questions about date and time formatting; how to create and manipulate dates, times, and time stamps; and how SEQUEL handles common date- and time-related functions. This article looks at these issues, plus how to extract date and time elements, and how to create durations.
Creating date/time/time stamp fields
You can create date, time, and time stamp data-type fields by using SEQUEL’s DATE, TIME, or TIMESTAMP functions. You also can use the CVTDATE function to convert numeric or character fields containing date values to a true date-type field. The syntax looks like this:
DATE(expr) TIME(expr) TIMESTAMP(expr[,expr])
For SEQUEL to work with the results of these expressions, they must be in a format SEQUEL recognizes (SAA, USA, ISO, JIS, EUR), or have the preferred date/time format and separator indicated in the DTSTYLE parameter. The second operand of the TIMESTAMP function specifies an optional time value for the resulting time stamp. If the second operand is not specified, the first operand must specify the entire time stamp. For example:
|
Expression |
Format |
|---|---|
| DATE("10/30/2009") | USA |
| DATE("11/30/09") | DTSTYLE(*MDY '/') specified |
| TIME("13.52.23") | ISO |
| TIME("05:00 PM") | USA |
| TIMESTAMP("2009-1-31","12:33 AM") | TIMESTAMP with optional time value operand specified |
| TIMESTAMP("2009-04-30-17.01.10.999999") | TIMESTAMP with single operand specifying entire time stamp |
| TIMESTAMP("20090430170110") | TIMESTAMP with single operand specifying entire time stamp |
Use CVTDATE to convert date values
SEQUEL’s Convert Date (CVTDATE) function automatically creates a date expression from numeric or character fields, taking some of the tedium out of working with dates. Here’s the format:
CVTDATE(expression,type)
The expression must contain a valid date in either numeric or character form. The type operand defines the format of the value to be converted. The following table shows the date formats that can be converted with CVTDATE:
| Type | Date Form | Example |
|---|---|---|
| MDY | mmddyy | 123109 |
| MDY1 | mmddyyyy | 12312009 |
| DMY | ddmmyy | 311209 |
| DMY1 | ddmmyyyy | 31122009 |
| YMD | yymmdd | 091231 |
| YMD1 | yyyymmdd | 20091231 |
| CYMD* | cyymmdd | 1091231 |
| JUL | yyddd | 09365 |
| JUL1 | yyyyddd | 2009365 |
| CJUL1* | cyyddd | 109365 |
*The date types CYMD and CJUL1 require a century digit preceding the year value. A century digit of zero (0) represents the years between 1900 and 1999, and a one (1) represents the years between 2000 and 2099. Date types without a century digit or a four-digit year (MDY, DMY, YMD, and JUL) are converted so that the year values between 40 and 99 become the years 1940 to 1999; the year values between 00 and 39 become the years 2000 to 2039.
You can use the CVTDATE function to convert date values stored in separate fields into a single data data-type. Use one of the following expressions (depending on the data in the fields):
CVTDATE(yy,mm,dd) CVTDATE(yyyy,mm,dd) CVTDATE(cc,yy,mm,dd)
This method of using the CVTDATE function converts three or four fields or expressions containing year, month, and day values to a date data type. The expressions must be specified in the order shown above. For example:
CVTDATE(odcdat,MDY) CVTDATE(cooyr,coomn,coody) Use CVTTIME to convert time values
You can use the Convert Time (CVTTIME) function to simplify creating valid time expressions from fields containing numeric or character values. It automatically creates a time expression from numeric or character fields.
CVTTIME has two formats. The first format accepts three character or numeric values. Each must be a valid two-digit number representing hour, minute, and second. The other format is a six-digit form (character or numeric) containing a time value in hhmmss format.
CVTTIME(hh,mm,ss) CVTTIME(hhmmss)
Retrieving date/time/time stamp data
Portions of a date, time, or time stamp expression can be extracted and returned as an integer using one of the following functions:
| Date/Time Stamp/Duration | |
| YEAR(expression) | returns the year (4,0) of a date |
| MONTH(expression) | returns the month (2,0) of a date |
| DAY(expression) | returns the day (2,0) of a date |
| DAYOFWEEK(expression) | returns 1-7 where Sunday=1 |
| DAYOFYEAR(expression) | returns 1-366 depending on date |
| QUARTER(expression) | returns 1-4 |
| WEEK(expression) | returns 1-53 |
| Time/Time Stamp/Duration | |
| HOUR(expression) | returns the hour (2,0) of a time |
| MINUTE(expression) | returns the minute (2,0) of a time |
| SECOND(expression) | returns the second (2,0)of a time |
| Time Stamp/Duration | |
| MICROSECOND(expression) | returns the microsecond (6,0) of a time stamp |
Understanding Duration
Up to this point, we have been looking at date/time/time stamp data types that represent an instant of time. To work successfully with date/time data types, you must understand another concept known as duration—a number representing an interval of time.
There are three types of durations:
- Date durations are packed numeric (8,0) values representing a number of years, months, and days. They have a yyyymmdd format.
- Time durations are numeric (6,0) values reflecting a number of hours, minutes, and seconds using an hhmmss format.
- Time stamp durations use a numeric (20,6) value representing a complete interval to microsecond precision. The complete form is yyyymm-ddhhmmss.mmmmmm.
Durations are useful in date/time arithmetic for incrementing and decrementing date/time values by an interval. You can create a duration by subtracting two columns of like data. For example:
CURRENT DATE - CVTDATE(invdate, ymd)
You can combine the DAYS function with the example above to extract the number of days between two dates or time stamp values as follows:
DAYS(CURRENT DATE)-DAYS(CVTDATE(invdate,ymd))
This example calculates the number of days between the current date and the invoice date value contained in the INVDATE field, which is converted from its YMD format.
Contributed by Steven Smith, Technical Consultant