Microsoft KB Archive/120472

{| = PRB: SELECT-SQL Using CMONTH Function Truncates Month Text =
 * width="100%"|

ID: Q120472

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5x, 2.6, 2.6a
 * Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6, 2.6a
 * Microsoft FoxPro for Macintosh, versions 2.5x, 2.6a

SYMPTOMS
In a SELECT-SQL operation using the CMONTH function on a date field, the resulting query table truncates the textual values returned by CMONTH. For example, "September" is truncated to "Septemb".

CAUSE
When a SELECT-SQL statement includes the CMONTH function, the resulting query table translates the defined field type from the source table to the necessary field type in the query table.

For example, if the source table has a field called "DATE" and it is defined as having a Date type with a field length of 8, FoxPro will translate the resulting output field that holds the results of the CMONTH operation into a Character type field. The first record returned by the SELECT-SQL operation will then govern the output field's length. If the first value returned is ten characters long, the resulting field definition will be of type Character with a length of 10. If the first value returned is four characters long, the resulting field definition will be of type Character with a length of 4. In such a situation, any subsequent values that are longer than the field definition will be truncated. This is the expected and normal behavior of a SELECT-SQL statement and is true of any calculated expression performed in a SELECT-SQL statement.

To demonstrate this behavior, execute the code shown below:

SET SAFETY OFF CLOSE DATABASES CREATE TABLE cmon (date D)

SELECT cmon FOR I = 1 TO 12 APPEND BLANK REPLACE cmon.date WITH CTOD(PADL(ALLTRIM(STR(I)),2,"0")+"/01/94") ENDFOR

&& After the first SELECT has been executed, press the ESC key && and it will process the second SELECT statement.

SELECT CMONTH(date) AS Month FROM cmon           && Truncated values SELECT CMONTH(date)+"      " AS Month FROM cmon  && Nontruncated values In FoxPro, the month of September may be truncated in the first SELECT statement since the underlying field definition in the Query table is defined as Character with a length definition matching the length of whatever the first value returned from the SELECT statement was. After you press the ESC key, the second SELECT statement will be displayed. By adding additional spaces to the CMONTH(field) expression, the underlying field MONTH will now be long enough to hold all the values returned from the SELECT-SQL statement.

RESOLUTION
To have sufficient length for the full text value returned by the CMONTH function, modify the SELECT-SQL statement by adding spaces to the CMONTH(field) function as shown in the following example:

SELECT CMONTH(date)+"    " as Month FROM cmon Appending spaces to the field that is being converted will add enough space to the field definition to hold the text month values returned by CMONTH. Additional reference words: FoxMac FoxDos FoxWin 2.00 2.50 2.50a 2.50b 2.50c 2.60 2.60a short cut off KBCategory: kbprg kbprb KBSubcategory: FxprgSql
 * }