PRB: SELECT-SQL Using CMONTH() Function Truncates Month Text

ID: Q120472

The information in this article applies to:

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

Last Reviewed: August 28, 1995