Here we cover the DATE data type and contrast the internal vs. the external representation of dates. The use of date fields in commands, in report statements, in Screen definitions, and in DBDs and AIs is discussed.
One important note: In all of the documentation below we are going to tell you that the external representation of a date is in MM/DD/YYYY form. This is the default date form for the USA culture. Thunderbolt automatically adapts the default external date form to your culture, so it may not be
MM/DD/YYYY for you. As you read this documentation you will have to make the mental translation to your culture's date form. For more information about cultural adaptations, see "Cultural Awareness
."
The DBD and DATE Fields
To define a date field just use the word DATE where you would normally put the type and length declaration. Date fields still support breakdowns, for example:
PDATE DATE @ PDATE is a DATE field in YYYYMMDD form
:1 year x(4) @ YEAR is a four character field
:2 cn x(2) @ CN is century
:2 yr x(2) @ YR is year
:1 mo x(2) @ MO is month
:1 dy x(2) @ DY is day
another example might be:
PDATE DATE @ PDATE is a DATE field in YYYYMMDD form
:1 yr x(4) @ YR is a four character year
:1 mo x(2) @ MO is month
:1 dy x(2) @ DY is day
or, without breakdowns, it's just:
PDATE DATE @ PDATE is a DATE field in YYYYMMDD form
The thing to notice is that a DATE is always (in all cultures) kept internally as YYYYMMDD form. As you will see below, there is a difference between the internal and external forms. When dates are displayed, or presented to you on a Screen or in a variable, they will appear in MM/DD/YYYY form. You will also see that the external format is controllable by you.
The AI and DATE Field Importing and Exporting
The syntax for a DATE data description statement in a Record Description is:
fieldname DATE ["override-template"]
The system standard date form for external representation of dates is
MM/DD/YYYY. So when you are describing a date field in an RD that defines the format of some outside file, Thunderbolt will assume that the actual date data exists in roughly that form. If it does not, you can supply a template describing its actual form. For the first example, lets take the case where you do not supply a template:
PDATE DATE
For importing (LOAD), since no template is specified, the incoming date data in the field named PDATE can be in any of these forms: mm/dd/yyyy,
mm-dd-yyyy, mm.dd.yyyy, mm dd yyyy, or mm/dd/yy, mm-dd-yy, mm.dd.yy, or mm dd yy. That is, either two or four character years are interpreted correctly, and the delimiter character can be a slash, dash, period, or blank.
For exporting (UNLOAD), the date will be sent to the external file in the standard default date form, that is, mm/dd/yyyy.
Another example of a date data description in an RD:
PDATE DATE "YYYYMMDD"
Here we are specifying that the date data in the external file is in the form yyyymmdd as shown by the template. Importing (LOAD) expects to find it in that form, and export (UNLOAD) will deliver it in that form. Note the quotes surrounding the template, they are required syntax.
By the way, you MUST use the YYYYMMDD form for DBF Record Descriptions as that is the DBF standard.
Another example of a date data description in an RD:
PDATE DATE "DD/MM/YY"
For both import (LOAD) or export (UNLOAD), the date data in the field named PDATE in the external file is in the form specified by the template.
Note the quotes surrounding the template, they are required syntax.
Whenever a slash appears in a template, incoming data can have dashes or periods or blanks as the actual delimiters and they will be recognized correctly. Outgoing data will always be delivered with the slash.
If the data description in an RD field that corresponds to a DBD DATE field is an X data type, then the data transfer during LOAD and UNLOAD will be the same as between two X data types. So the date would be delivered as YYYYMMDD.
If the data description in an RD field that corresponds to a DBD DATE field is an N data type, then the numeric field is interpreted as a Julian date and automatic conversion will occur when data is transferred either way.
When you do not use a format override in any of the above commands the sytem standard default external date form MM/DD/YYYY is what is presented to you on output, and is expected in input.
RAWPRINT does not support format overrides, so when you RPR a date field it comes out in MM/DD/YYYY form.
An example of the use of a format override:
PRINT A SALES DESCR PDATE ("MM/DD/YY") QUAN_BOT PRICE
Notice the form of the format override: The override template is surrounded by quotes, and then it is contained in parenthesis. This is the way a date format override would appear in all commands.
FETCH, MOVE, SET: These all deliver date fields in MM/DD/YYYY form unless an override is specified. For examples:
In a procedure:
FETCH A SALES PDATE to %V ("yyyymmdd")
In a report:
SET %V = PDATE ("yyyymmdd")
In a screen Special Procedure:
MOVE PDATE to %V ("yyyymmdd")
MOVE %V ("yyyymmdd") to PDATE
Note in the second MOVE the assumed form in the variable is MM/DD/YYYY so the format override was neccessary to tell Thunderbolt its actual form.
In the case of FETCH, SET, and MOVE if the variable involved is a numeric variable rather than a string variable, the date is delivered in Julian form. If %V was a numeric variable in the above examples, no format overrides would have been needed and julian conversions automatically occur.
WRITE, CHANGE, and REWRITE: The values in string variables or literals that are going to DATE fields are assumed to be MM/DD/YYYY form unless you specify a format override. For example:
CHANGE A SALES PDATE TO {%V} ("yyyymmdd")
The WRITE, CHANGE, and REWRITE support the use of variables directly, that is, without the curly braces that indicate "value of." In this case, if the variable is a numeric variable it is assumed to be a Julian date. For example:
CHANGE A SALES PDATE TO %N
where %N is a numeric variable containing a Julian date.
The FIND Command and DATE Fields
There is no format override concept in the FIND command syntax, but the FIND command does support external date forms in its expressions. Some examples of FIND:
FIND A WHERE PDATE GE 12/15/1992 THRU 1/5/1993
FIND A WHERE PDATE GE 12/15/92 THRU 1/5/93
FIND A WHERE PDATE GE 19921215 THRU 19930105
All of these are legitimate query expressions. The first two are using external date forms and the third uses the internal date form. Thunderbolt tells the difference by the fact that the latter form employs no delimiter character (i.e., no slash).
The syntactical rule for FIND is that when an external form of the date is being used, delimiter characters (either a slash, dash, or period) will be present. Both the MM/DD/YYYY and MM/DD/YY forms are supported. If no delimiter character is present, you MUST be using the internal form YYYYMMDD.
When using the SV operator, always use the internal form. SV does not support any external form.
IF in Reports and DATE Fields
When using an IF test directly against a date field in a report definition, only the internal YYYYMMDD form is supported for string compares.
IF PDATE = '19930101' -Note the YYYYMMDD form.
IF PDATE = %V -The value in %V must be YYYYMMDD form if
%V is a string variable, or, if %V is a numeric
variable, the value in %V must be Julian.
FIELD SERVICES "Must be in LIST/RANGE" and DATE Fields
Values for dates in the MUST BE IN LIST field service must be given in the internal YYYYMMDD form and they cannot include comment information.
When using the MUST BE IN RANGE field service, the values supplied for date fields must be in the YYYYMMDD form.
