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

 Last reviewed: August 28, 1995
Article 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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 28, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.